SQL/MX 2.x Reference Manual (G06.24+, H06.03+)
SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-26
TRANSPOSE Clause
transpose-item-list
specifies a list of items. An item is either a value expression or a list of value
expressions enclosed in parentheses.
expression-list
specifies a list of SQL value expressions, separated by commas. The
expressions must have compatible data types.
For example, in the transpose set TRANSPOSE A,B,C AS V, the
expressions A,B, and C have compatible data types.
(expression-list) [,(expression-list)]...
specifies a list of expressions enclosed in parentheses, followed by
another list of expressions enclosed in parentheses, and so on. The
number of expressions within parentheses must be equal for each list. The
expressions in the same ordinal position within the parentheses must have
compatible data types.
For example, in the transpose set TRANSPOSE (A,X),(B,Y),(C,Z) AS
(V1,V2), the expressions A,B, and C have compatible data types, and the
expressions X,Y, and Z have compatible data types.
transpose-col-list
specifies the columns that consist of the evaluation of expressions in the item
list as the expressions are applied to rows of the source table.
colname
is an SQL identifier that specifies a column name. It identifies the column
consisting of the values in expression-list.
For example, in the transpose set TRANSPOSE A,B,C AS V, the column
V corresponds to the values of the expressions A,B, and C.
(colname-list)
specifies a list of column names enclosed in parentheses. Each column
consists of the values of the expressions in the same ordinal position within
the parentheses in the transpose item list.
For example, in the transpose set TRANSPOSE (A,X),(B,Y),(C,Z) AS
(V1,V2), the column V1 corresponds to the expressions A,B, and C, and
the column V2 corresponds to the expressions X,Y, and Z.
KEY BY key-colname
optionally specifies which expression (the value in the transpose column list
corresponds to) by its position in the item list. key-colname is an SQL identifier.
The data type of the key column is exact numeric, and the value is NOT NULL.