Neoview SQL Reference Manual (R2.2)

The result table of the TRANSPOSE query is:
VALCOLKEYCOLFEDCBA
11f1e1d1100101
102f1e1d1100101
1003f1e1d1100101
21f2e2d2200202
202f2e2d2200202
2003f2e2d2200202
• 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:
CBAVALCOLKEYCOL
100101111
1001011032
10010163
200202221
2002022032
20020263
• 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:
COUNT(*)DVALCOLKEYCOL
1d111
1d1102
1d11003
1d221
1d2202
1d22003
• 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;
274 SQL Clauses