Maybe with a query or two extra, you can determine a temp table, then build on that. Do an initial distinct look up on the primary fields you want as the fields in your temp table and create it, then do the required queries to get the raw data into the temp table, then do the finalized query to get the results you need. I can't think of a single step method to get what you want done. Any time I've had to take (what I call) vertical data and convert it to a horizontal format, I've always had to construct either an array in memory or create another table.
On Mon, Feb 9, 2015 at 7: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. > <snip> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
