This looks interesting: http://search.cpan.org/~bduggan/SQLite-VirtualTable-Pivot-0.02/lib/SQLite/VirtualTable/Pivot.pm it is a bit old - 2009 . Other SQL dialects have this either built in or via an "extension". If you're doing this in a program, I would likely just to two SELECT statements. The first gets the "Group" values and constructs the next SELECT based on it using what you showed as an example when the column names are known.
On Mon, Feb 9, 2015 at 6:55 AM, R.Smith <rsm...@rsweb.co.za> wrote: > I used to make Pivot-table-like reports in (what I thought was) the normal > way: > > SELECT ProductID, sum(QtyMade) AS TotQty, > (sum(CASE Mth WHEN 1 THEN QtyMade ELSE 0 END)) AS 'Jan Qty' > ,(sum(CASE Mth WHEN 2 THEN QtyMade ELSE 0 END)) AS 'Feb Qty' > ,(sum(CASE Mth WHEN 3 THEN QtyMade ELSE 0 END)) AS 'Mar Qty' > ... > ,(sum(CASE Mth WHEN 11 THEN QtyMade ELSE 0 END)) AS 'Nov Qty' > ,(sum(CASE Mth WHEN 12 THEN QtyMade ELSE 0 END)) AS 'Dec Qty' > FROM ProductionHist > WHERE Year=2014 > GROUP BY ProductID > ORDER BY TotQty DESC; > > etc. > > Which of course works fine if you have a predeterminable set of columns to > extract, such as Months in the above case. How can I do this sort of thing > (i'm only interested in similar results, the method is not important) for > an indetermintate set of columns? > > Example data: > ID | Product | Group| Qty > ----+----------+--------+------- > 1 | ABC001 | A | 10 > 2 | ABC001 | A | 20 > 3 | ABC002 | A | 10 > 4 | ABC001 | B | 10 > 5 | ABC002 | B | 20 > 6 | ABC001 | F | 60 > ... > 97 | ABC001 | n | 20 > 98 | ABC002 | n | 30 > > To produce a result like: > > Product | Tot Group A | Tot Group B | ... | Tot Group n > --------+---------------+---------------+-----+------------- > ABC001 | 30 | 10 | ... | 20 > ABC002 | 10 | 20 | ... | 30 > > > Both Products and Groups are indeterminate. I toyed with the idea of > making a temporary table or CTE with DISTINCT to contain only unique values > for one set (either Procucts or Groups in the above example) and then maybe > derive another table and finally a "SELECT * " query which would simply > reproduce all, but of course that won't work - somewhere along the line you > need a handle on the number of output columns. > > I know this is easy in a program or in a spreadsheet, but I am trying to > produce some queries without such dependancies. If anyone has an idea how > to achieve something like this (even if very technical or convoluted) it > would be much appreciated. > > Thanks, > Ryan > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
