SQL query Question

Question

I'm currently learning SQL and have some issues figuring how to do the following:

Tables:

Planned Order
    id  Item    Date
    0   1   2011-01-24
    1   1   2011-01-26
    2   1   2011-01-28
    3   2   2011-01-24
    4   3   2011-01-27

Customer Order
    id  Item    Date
    4   2   2011-01-25
    3   2   2011-01-24
    2   2   2011-01-24
    1   1   2011-01-26
    0   1   2011-01-24

I'm trying to produce the following query that will produce:

Item  Category          2011-01-24      2011-01-25   2011-01-26   
1     Customer_Order    1               NULL         1      
1     Planned_Order     1               NULL         1
2     Customer_Order    2               NULL         NULL
2     Planned_Order     1               NULL         NULL
3     Planned_Order     NULL            NULL         NULL

Where the count under the date is how many times the item was ordered that day.

Is this even possible with raw sql code or is this? I realize this can be done through other languages such as perl to manipulate the data and through multiple data base access, but I wanted to do this directly from raw sql.

I can't find a command(raw sql) to convert a query from a column and convert it to a row. How I query the dates and produce column for each date like seen above.

Answer 1

In Sql Server (2005/2008), there´s an operator to do exactly what you need (PIVOT)

SELECT Item,Category,[2011-01-24], [2011-01-25], [2011-01-26]
FROM
(SELECT Item, 'Planned_Order' Category, Date
 FROM   [Planned Order]
 union all
 SELECT Item, 'Customer_Order', Date
 FROM   [Customer Order]
 ) AS SourceTable
PIVOT
(
COUNT(*)
FOR Date IN ([2011-01-24], [2011-01-25], [2011-01-26])
) AS PivotTable;

Answer 2

Portable way to do Pivoting:

select item, 'Customer_Order' Category,
  COUNT(case when Date='2011-01-24' then 1 else 0 end) as `2010-01-24`,
  COUNT(case when Date='2011-01-25' then 1 else 0 end) as `2010-01-25`,
  COUNT(case when Date='2011-01-26' then 1 else 0 end) as `2010-01-26`
from customer_order
GROUP BY item
union all
select item, 'Planned_Order' Category,
  COUNT(case when Date='2011-01-24' then 1 else 0 end) as `2010-01-24`,
  COUNT(case when Date='2011-01-25' then 1 else 0 end) as `2010-01-25`,
  COUNT(case when Date='2011-01-26' then 1 else 0 end) as `2010-01-26`
from planned_order
GROUP BY item
ORDER BY item, Category

The problem is that you need to know all the dates you want to display up front. The only DBMS (I know of) to support a dynamic list of columns is Oracle. Other DBMS will require you to generate dynamic SQL to create the CASE statements, one for each distinct date encountered.

The only non-portable part is that because of using dates as field names, you need

`2010-01-24` backticks here for MySQL
"2010-01-24" or [2010-01-24] for SQL Server
"2010-01-24" for Oracle
etc