Pivoting SQLite table, setwise like SQL should be
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.

That's simple enough -   `SELECT COUNT(*), name, strf("%W:%Y", time, "unixepoch") FROM events GROUP BY strf("%W:%Y", time, "unixepoch"), name ORDER BY time'

and we get about six thousand rows of data.   
count          name        week:year  
23............ fudge.......23:2009  
etc...

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:

Name      23:2009       24:2009    25:2009  
fudge........23............6............19  
fish.........1.............0............12  

etc...

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.
Pivoting SQLite table, setwise like SQL should be
===

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.

That's simple enough -   `SELECT COUNT(*), name, strf("%W:%Y", time, "unixepoch") FROM events GROUP BY strf("%W:%Y", time, "unixepoch"), name ORDER BY time'

and we get about six thousand rows of data.   
count          name        week:year  
23............ fudge.......23:2009  
etc...

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:

Name      23:2009       24:2009    25:2009  
fudge........23............6............19  
fish.........1.............0............12  

etc...

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.