SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Clauses
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
7-31
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
1 11 1 10 100
2 103 1 10 100
3 6110 100
1 22 2 20 200
2 203 2 20 200
3 6220 200
KEYCOL VALCOL D COUNT(*)
11d11
2 10d11
3100d11
12d21
2 20d21
3200d21