Ask the Expert

Displaying histograms

The database table looks like this
|-------+---------+----+----|
|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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: