SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-25
TRANSPOSE Clause
TRANSPOSE Clause
Considerations for TRANSPOSE
Examples of TRANSPOSE
The TRANSPOSE clause of the SELECT statement generates for each row of the
SELECT source table a row for each item in the transpose item list. The result table of
the TRANSPOSE clause has all the columns of the source table plus, for each
transpose item list, a value column or columns and an optional key column.
TRANSPOSE is an SQL/MX extension.
transpose-item-list AS transpose-col-list
specifies a transpose-set, which correlates a transpose-item-list with a
transpose-col-list. The transpose-item-list can be either a list of
expressions or a list of expression lists enclosed in parentheses. The transpose-
col-list can be either a single column name or a list of column names enclosed
in parentheses.
For example, in the transpose-set TRANSPOSE (A,X),(B,Y),(C,Z) AS
(V1,V2), the items in the transpose-item-list are (A,X),(B,Y), and
(C,Z), and the transpose-col-list is (V1,V2). The number of expressions
in each item must be the same as the number of value columns in the column list.
In the example TRANSPOSE A,B,C AS V, the items are A,B, and C, and the
value column is V. This form can be thought of as a shorter way of writing
TRANSPOSE (A),(B),(C) AS (V).
TRANSPOSE transpose-set [transpose-set]...
[KEY BY key-colname]
transpose-set is:
transpose-item-list AS transpose-col-list
transpose-item-list is:
expression-list
| (expression-list) [,(expression-list)]...
expression-list is:
expression [,expression]...
transpose-col-list is:
colname
| (colname-list)
colname-list is:
colname [,colname]...