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

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-28
Considerations for TRANSPOSE
Cardinality of the TRANSPOSE Result
The items in each transpose-item-list are enumerated from 1 to N, where N is
the total number of items in all the item lists in the transpose sets.
In this example with a single transpose set, the value of N is 3:
TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2)
In this example with two transpose sets, the value of N is 5:
TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2)
L,M AS V3
The values 1 to N are the key values k
i
. The items in each transpose-item-list
are the expression values v
i
.
The cardinality of the result of the TRANSPOSE clause is the cardinality of the source
table times N, the total number of items in all the transpose item lists.
For each row of the source table and for each value in the key values k
i
, the
TRANSPOSE result contains a row with all the attributes of the source table, the key
value k
i
in the key column, the expression values v
i
in the value columns of the
corresponding transpose set, and NULL in the value columns of other transpose sets.
For example, consider this TRANSPOSE clause:
TRANSPOSE (A,X),(B,Y),(C,Z) AS (V1,V2)
L,M AS V3
KEY BY K
The value of N is 5. One row of the SELECT source table produces this TRANSPOSE
result:
columns-of-source KV1 V2 V3
source-row 1 value-of-A value-of-X NULL
source-row 2 value-of-B value-of-Y NULL
source-row 3 value-of-C value-of-Z NULL
source-row 4 NULL NULL value-of-L
source-row 5 NULL NULL value-of-M