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
