SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-49
Converting Long, Narrow Tables Into Short, Wide
Ones
Converting Long, Narrow Tables Into Short, Wide Ones
You might find that you can manipulate data easily when you use long, narrow tables,
but for reports, you may prefer short, wide tables.
Suppose that you have a table that contains each salesperson’s name, a month
number, and a bonus amount for that month. No amounts are null and all are numeric.
The primary key is NAME and MONTH. Twelve rows represent the amounts for a year
for one salesperson, as in this example:
NAME MONTH AMOUNT
-------- ------ --------------------
CHIN 1 200
CHIN 2 0
CHIN 3 0
CHIN 4 0
CHIN 5 400
CHIN 6 120
CHIN 7 80
CHIN 8 220
CHIN 9 115
CHIN 10 130
CHIN 11 75
CHIN 12 105
KAPOOR 1 200
KAPOOR 2 0
KAPOOR 3 150
KAPOOR 4 300
KAPOOR 5 0
KAPOOR 6 110
KAPOOR 7 20
KAPOOR 8 130
KAPOOR 9 0
KAPOOR 10 50
KAPOOR 11 200
KAPOOR 12 110
KLEIN 1 100
KLEIN 2 60
KLEIN 3 220
KLEIN 4 230
KLEIN 5 210
KLEIN 6 40
KLEIN 7 120
KLEIN 8 140
KLEIN 9 20
KLEIN 10 150
KLEIN 11 0
KLEIN 12 60
--- 36 row(s) selected.