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

SQL/MX Clauses

HP NonStop SQL/MX Reference Manual—540440-003

7-27

Considerations for TRANSPOSE

Multiple TRANSPOSE Clauses and Sets

•

Multiple TRANSPOSE clauses can be used in the same query. For example:

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

•

A TRANSPOSE clause can contain multiple transpose sets. For example:

SELECT KEYCOL, VALCOL1, VALCOL2 FROM MYTABLE

TRANSPOSE A, B, C AS VALCOL1

D, E, F AS VALCOL2

KEY BY KEYCOL

Degree and Column Order of the TRANSPOSE Result

The degree of the TRANSPOSE result is the degree of the source table (the result

table derived from the table reference or references in the FROM clause and a

WHERE clause if specified), plus one if the key column is specified, plus the

cardinalities of all the transpose column lists.

The columns of the TRANSPOSE result are ordered beginning with the columns of the

source table, followed by the key column if specified, and then followed by the list of

column names in the order in which they are specified.

Data Type of the TRANSPOSE Result

The data type of each of the value columns is the union compatible data type of the

corresponding expressions in the transpose-item-list. You cannot have

expressions with data types that are not compatible in a transpose-item-list.

For example, in TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2), the data type of

V1 is the union compatible type for A, B, and C, and the data type of V2 is the union

compatible type for X, Y, and Z.

See Comparable and Compatible Data Types on page 6-16.