SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-36
CASE for Converting Long, Narrow Tables Into
Short, Wide Ones
CASE for Converting Long, Narrow Tables Into Short, Wide Ones
Following are the create statement and the query in Converting Long, Narrow Tables
Into Short, Wide Ones on page 1-49.
create table bonus
(name char(30),
month smallint NOT NULL,
amount largeint NOT NULL,
primary key (name, month));
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;
Example 6-22 on page 6-37 shows the EXPLAIN plan.