take rows as columns in pivot table in sqlite

Question

I have this sql select statement:

Select ORDERS.ptDate as Date,Products.Description as Product, 
            ORDERDETAILS2.Quantity   FROM         ORDERS inner join ORDERDetails2 on Orders._id=ORDERDetails2.PtOrder inner join Products on Orderdetails2.ptproduct=Products.PtUnique order by date

which retutrns this kind of data:

2014-03-19 00:00:00;"Product a";"4"
2014-03-19 00:00:00;"Product c";"750"
2014-03-19 00:00:00;"Product a";"1"
2014-03-19 00:00:00;"Product b";"1"
2014-03-19 00:00:00;"Product c";"1075"
2014-03-20 00:00:00;"Product b";"2"
2014-03-20 00:00:00;"Product b";"1"
2014-03-20 00:00:00;"Product a";"2"
2014-03-20 00:00:00;"Product c";"2"
2014-03-20 00:00:00;"Product b";"25"

can I reproduce this having dates as columns , second column as rows and third column as aggegate function in sqlite? The result should be this:

Product      2014-03-19    2014-03-20
Product a        5              2
Product b        1              28
Product c      1825             2

Thanks for help

possible duplicate of [SQL query to pivot a column using CASE WHEN](http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when) — cha, Mar 20 '14 at 23:01
I don't know from start how many dates will be at table. So i can't use select case(I think) — albertsmus, Mar 20 '14 at 23:11
If you do not know the number of dates you will have to make a dynamic query. SQLite does not have a built-in support for dynamic queries, so you will have to do this on an application level (i.e. in the code) — cha, Mar 20 '14 at 23:12

score 0 · Answer 1 · answered Mar 21 '14 at 09:48

You will have to make a dynamic query, i.e., you have to generate the SQL code for each date.

First, get the dates:

SELECT DISTINCT ptDate
FROM Orders
ORDER BY ptDate

Then, begin with a query that returns one record for each desired product:

SELECT Description AS Product
FROM Products

(If you want to restrict this to products in some specific orders, you have to do joins here, or use WHERE PtUnique IN (...).)

Then write a query that combines these products with one column for each date. These values must be computed with correlated subqueries:

SELECT Description AS Product,
       (SELECT SUM(OrderDetails2.Quantity)
        FROM OrderDetails2
        JOIN Orders ON OrderDetails2.PtOrder = Orders._id
        WHERE Orders.ptDate = '2014-03-19'
          AND OrderDetails2.PtProduct = Products.PtUnique
       ) AS "2014-03-19",
       (SELECT SUM(OrderDetails2.Quantity)
        FROM OrderDetails2
        JOIN Orders ON OrderDetails2.PtOrder = Orders._id
        WHERE Orders.ptDate = '2014-03-20'
          AND OrderDetails2.PtProduct = Products.PtUnique
       ) AS "2014-03-20"
FROM Products

In code i created a sqldatareader taking dates as you advised, and after i created a string this way: While sqReader.Read() codIds = codIds & "," & "Sum(case when date=" & "'" & sqReader.GetString(sqReader.GetOrdinal("ptdate")) & "'" & " then (quantity)end) as " & "'" & sqReader.GetString(sqReader.GetOrdinal("ptdate")).Substring(0, 10) & "'" End While after i combined this string with this way: pSql = "Select Product " pSql = pSql & codIds pSql = pSql & " FROM Tameio1 group by Product" so it worked. — albertsmus, Mar 21 '14 at 18:40

take rows as columns in pivot table in sqlite

1 Answers1