SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-50
Converting Long, Narrow Tables Into Short, Wide
Ones
To produce a report with a row for each salesperson’s name and corresponding bonus
amounts for months 1 through 12, you can use CASE with GROUP BY:
set list_count 0 ;
select name,
SUM(CASE when month = 1 then amount else 0 END),
SUM(CASE when month = 2 then amount else 0 END),
SUM(CASE when month = 3 then amount else 0 END),
SUM(CASE when month = 4 then amount else 0 END),
SUM(CASE when month = 5 then amount else 0 END),
SUM(CASE when month = 6 then amount else 0 END),
SUM(CASE when month = 7 then amount else 0 END),
SUM(CASE when month = 8 then amount else 0 END),
SUM(CASE when month = 9 then amount else 0 END),
SUM(CASE when month = 10 then amount else 0 END),
SUM(CASE when month = 11 then amount else 0 END),
SUM(CASE when month = 12 then amount else 0 END)
from bonus
group by name;
detail name,
col 2 as I3 heading "JAN",
col 3 as I3 heading "FEB",
col 4 as I3 heading "MAR",
col 5 as I3 heading "APR",
col 6 as I3 heading "MAY",
col 7 as I3 heading "JUN",
col 8 as I3 heading "JUL",
col 9 as I3 heading "AUG",
col 10 as I3 heading "SEP",
col 11 as I3 heading "OCT",
col 12 as I3 heading "NOV",
col 13 as I3 heading "DEC";
list all;
NAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
-------- --- --- --- --- --- --- --- --- --- --- --- ---
CHIN 200 0 0 0 400 120 80 220 115 130 75 105
KAPOOR 200 0 150 300 0 110 20 130 0 50 200 110
KLEIN 100 60 220 230 210 40 120 140 20 150 0 60
--- 3 row(s) selected.
If required, you could also calculate totals for each month and a yearly total for each
salesperson.
For the EXPLAIN plan for the query in the example, see CASE for Converting Long,
Narrow Tables Into Short, Wide Ones on page 6-36. For information on SUM, see
Aggregate Functions in Predicates on page 1-41. For information on GROUP BY, see
The GROUP BY Clause on page 1-7.