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

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-31
Examples of TRANSPOSE
•
This query shows how to use COUNT applied to VALCOL. The result table of the
TRANSPOSE query shows the number of distinct values in VALCOL.
SELECT COUNT(DISTINCT VALCOL) FROM mytable
TRANSPOSE A, B, C AS VALCOL
KEY BY KEYCOL
GROUP BY KEYCOL;
(EXPR)
--------------------
2
2
2
--- 3 row(s) selected.
•
This query shows how multiple TRANSPOSE clauses can be used in the same
query. The result table from this query has nine times as many rows as there are
rows in MYTABLE:
SELECT KEYCOL1, VALCOL1, KEYCOL2, VALCOL2 FROM mytable
TRANSPOSE A, B, C AS VALCOL1
KEY BY KEYCOL1
TRANSPOSE D, E, F AS VALCOL2
KEY BY KEYCOL2;
The result table of the TRANSPOSE query is:
KEYCOL1 VALCOL1 KEYCOL2 VALCOL2
111d1
112e1
113f1
2101d1
2102e1
2103f1
31001d1
31002e1
31003f1
121d2
122e2
123f2
2201d2
2202e2
2203f2
32001d2
32002e2
32003f2