Q

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    |      |      |      |     |
|----------+------+------+------+------+------+------+------+------+------+-----|

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close