[Tutor] How to create a sqlite table schema dynamically

[Alan Gauld]

On 20/03/14 22:39, Toni Fuente wrote:

>> I would expect your table to have only 3 columns:
>>
>> week, Name, quantity.
>>
>> You then run your report with something like
>>
>> select name, count from os
>> where week == '15'
>>
> Aha, and then produce the report in the way, that I am trying to build
> the table?
>
> Week, Redhat, CentOS 6, CentOS 5, Debian Squeeze, Debian Whezzy, ..., Ubuntu, Solaris, Windows XP, Windows 7
>
> 13      4       6          5            3               5               8       4         4             8
> 14      3       7          4            3               5               7       4         4             4


OK, Then I'd use something like

select week, name, count from os ordered by week.

That gives you all the rows in week order so all
the os values for a given week are together.

You can then iterate over each week group and reformat
as you require to get your table above.

Or use Peter's approach which I just spotted ;-)

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.flickr.com/photos/alangauldphotos