title: Pivoting SQLite table, setwise like SQL should be
body: <p>I have some data. 224,000 rows of it, in a SQLite database. I want to extract time series information from it to feed a data visualisation tool. Essentially, each row in the db is an event that has (among other things not strictly relevant) a time-date group in seconds since the epoch and a name responsible for it. I want to extract how many events each name has for every week in the db.</p>
  
  <p>That's simple enough:</p>
  
  <pre><code>SELECT COUNT(*), 
         name, 
         strf("%W:%Y", time, "unixepoch") 
    FROM events 
   GROUP BY strf("%W:%Y", time, "unixepoch"), name 
   ORDER BY time
  </code></pre>
  
  <p>and we get about six thousand rows of data. </p>
  
  <pre><code>count          name        week:year  
  23............ fudge.......23:2009  
  etc...
  </code></pre>
  
  <p>But I don't want a row for each name in each week - I want a row for each name, and a column for each week, like this:</p>
  
  <pre><code>Name      23:2009       24:2009    25:2009  
  fudge........23............6............19  
  fish.........1.............0............12  
  etc...
  </code></pre>
  
  <p>Now, the monitoring process has been running for 69 weeks, and the count of unique names is 502. So clearly, I'm far from keen on any solution that involves hardcoding all the columns and still less the rows. I'm less unkeen on anything that involves iterating over the lot, say with python's executemany(), but I'm willing to accept it if necessary. SQL is meant to be set-wise, dammit.</p>