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

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-30
Examples of TRANSPOSE
•
This query shows that the items in the transpose item list can be any valid scalar
expressions:
SELECT KEYCOL, VALCOL, A, B, C FROM mytable
TRANSPOSE A + B, C + 3, 6 AS VALCOL
KEY BY KEYCOL;
The result table of the TRANSPOSE query is:
•
This query shows how the TRANSPOSE clause can be used with a GROUP BY
clause. This query is typical of queries used to obtain cross-table information,
where A, B, and C are the independent variables, and D is the dependent variable.
SELECT KEYCOL, VALCOL, D, COUNT(*) FROM mytable
TRANSPOSE A, B, C AS VALCOL
KEY BY KEYCOL
GROUP BY KEYCOL, VALCOL, D;
The result table of the TRANSPOSE query is:
KEYCOL VALCOL A B C
111110100
2103110100
3 6110 100
122220200
2203220200
3 6220 200
KEYCOL VALCOL D COUNT(*)
11d11
2 10d11
3100d11
12d21
2 20d21
3200d21