SQL query Question

Question

I am currently learning SQL and have some problems regarding 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 am trying to create 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

If the number below the date indicates how many times the item was ordered on that day.

Is this possible with raw sql code or is it? I understand that this can be done using other languages ​​like perl to manipulate data and through multiple access to the database, but I wanted to do this directly from raw sql.

I cannot find a command (raw sql) to convert a query from a column and convert it to a string. As I request dates and produce a column for each date, as shown above.

Answer 1

Sql Server (2005/2008) , (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

:

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

, , . ( ) Oracle. SQL CASE, .

, -

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