|-------+---------+----+----| |PartNo |Date |P/O |Qty | |-------+---------+----+----| |1111 |1/1/2000 |aaa |11 | |-------+---------+----+----| |1111 |2/1/2000 |bbb |22 | |-------+---------+----+----| |1111 |4/1/2002 |ccc |33 | |-------+---------+----+----| |2222 |4/1/2002 |ddd |44 | |-------+---------+----+----| |2222 |5/1/2002 |eee |55 | |-------+---------+----+----| |2222 |8/1/2002 |fff |66 | |-------+---------+----+----|I need a DB2 SQL query to display the data in this format:
|----------+------+------+------+------+------+------+------+------+------+-----| |PartNumber| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |----------+------+------+------+------+------+------+------+------+------+-----| |1111 |aaa |bbb | - |ccc | - | - | - | - | - | - | | |11 |22 | |33 | | | | | | | |----------+------+------+------+------+------+------+------+------+------+-----| |2222 | - | - | - |ddd |eee |fff | - | - | - | - | | | | | |44 |55 |66 | | | | | |----------+------+------+------+------+------+------+------+------+------+-----|
Requires Free Membership to View
Let me start by first saying that SQL is not the preferred tool for presenting a crosstab, which is what you've illustrated. Reporting tools are much better suited for such formatting. However, this type of question is a common one, so I'll answer it as best I can. By the way, I'm using Oracle flavored SQL; you'll have to translate for your particular DBMS. I'll start with a simple table definition and some data:
create table Orders ( PartNo numeric(5), OrderDate date, PO varchar(15), Qty numeric(5) ); insert into Orders values (1111,'01-JAN-2002','aaa',11); insert into Orders values (1111,'01-FEB-2000','bbb',22); insert into Orders values (1111,'01-APR-2002','ccc',33); insert into Orders values (2222,'01-APR-2002','ddd',44); insert into Orders values (2222,'01-MAY-2002','eee',55); insert into Orders values (2222,'01-JUN-2002','fff',66);Here's the SQL statement:
select PartNo,
max(case when to_char(OrderDate,'MON') = 'JAN' then PO end) as Jan_PO,
sum(case when to_char(OrderDate,'MON') = 'JAN' then Qty end) as Jan_Qty,
max(case when to_char(OrderDate,'MON') = 'FEB' then PO end) as Feb_PO,
sum(case when to_char(OrderDate,'MON') = 'FEB' then Qty end) as Feb_Qty,
max(case when to_char(OrderDate,'MON') = 'MAR' then PO end) as Mar_PO,
sum(case when to_char(OrderDate,'MON') = 'MAR' then Qty end) as Mar_Qty,
max(case when to_char(OrderDate,'MON') = 'APR' then PO end) as Apr_PO,
sum(case when to_char(OrderDate,'MON') = 'APR' then Qty end) as Apr_Qty,
max(case when to_char(OrderDate,'MON') = 'MAY' then PO end) as May_PO,
sum(case when to_char(OrderDate,'MON') = 'MAY' then Qty end) as May_Qty,
max(case when to_char(OrderDate,'MON') = 'JUN' then PO end) as Jun_PO,
sum(case when to_char(OrderDate,'MON') = 'JUN' then Qty end) as Jun_Qty,
max(case when to_char(OrderDate,'MON') = 'JUL' then PO end) as Jul_PO,
sum(case when to_char(OrderDate,'MON') = 'JUL' then Qty end) as Jul_Qty,
max(case when to_char(OrderDate,'MON') = 'AUG' then PO end) as Aug_PO,
sum(case when to_char(OrderDate,'MON') = 'AUG' then Qty end) as Aug_Qty,
max(case when to_char(OrderDate,'MON') = 'SEP' then PO end) as Sep_PO,
sum(case when to_char(OrderDate,'MON') = 'SEP' then Qty end) as Sep_Qty,
max(case when to_char(OrderDate,'MON') = 'OCT' then PO end) as Oct_PO,
sum(case when to_char(OrderDate,'MON') = 'OCT' then Qty end) as Oct_Qty,
max(case when to_char(OrderDate,'MON') = 'NOV' then PO end) as Nov_PO,
sum(case when to_char(OrderDate,'MON') = 'NOV' then Qty end) as Nov_Qty,
max(case when to_char(OrderDate,'MON') = 'DEC' then PO end) as Dec_PO,
sum(case when to_char(OrderDate,'MON') = 'DEC' then Qty end) as Dec_Qty
from Orders
group by PartNo, PO;
Using the CASE statement allows us to flatten out the results so that they fall in the right columns. Using MAX and SUM and GROUPing by PartNo and PO allows us to combine like POs. Here are the resulting rows (for brevity, I'm only showing the rows through April):PARTNO JAN_PO JAN_QTY FEB_PO FEB_QTY MAR_PO MAR_QTY APR_PO APR_QTY ------ ------ ------- ------ ------- ------ ------- ------ ------- 1111 aaa 11 1111 bbb 22 1111 ccc 33 2222 ddd 44 2222
This was first published in July 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation