SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-32
Examples of TRANSPOSE
•
This query shows how a TRANSPOSE clause can contain multiple transpose
sets—that is, multiple transpose-item-list AS transpose-col-list. The
expressions A, B, and C are of type integer, and expressions D, E, and F are of
type character.
SELECT KEYCOL, VALCOL1, VALCOL2 FROM mytable
TRANSPOSE A, B, C AS VALCOL1
D, E, F AS VALCOL2
KEY BY KEYCOL;
The result table of the TRANSPOSE query is:
A question mark (?) in a value column indicates no value for the given KEYCOL.
•
This query shows how the preceding query can include a GROUP BY clause:
SELECT KEYCOL, VALCOL1, VALCOL2, COUNT(*) FROM mytable
TRANSPOSE A, B, C AS VALCOL1
D, E, F AS VALCOL2
KEY BY KEYCOL
GROUP BY KEYCOL, VALCOL1, VALCOL2;
The result table of the TRANSPOSE query is:
KEYCOL VALCOL1 VALCOL2
11 ?
210?
3100?
4? d1
5? e1
6? f1
12 ?
220?
3200?
4? d2
5? e2
6? f2
KEYCOL VALCOL1 VALCOL2 (EXPR)
11 ? 1
210? 1
3100? 1
12 ? 1
220? 1
3200? 1
4? d21