Neoview SQL Reference Manual (R2.2)

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 ki. The items in each transpose-item-list are the
expression values vi.
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 ki, the TRANSPOSE result
contains a row with all the attributes of the source table, the key value ki in the key column, the
expression values vi 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:
V3V2V1Kcolumns-of-source
NULL
value-of-Xvalue-of-A
1
source-row
NULL
value-of-Yvalue-of-B
2
source-row
NULL
value-of-Zvalue-of-C
3
source-row
value-of-L
NULLNULL4
source-row
value-of-M
NULLNULL5
source-row
Examples of TRANSPOSE
Suppose that MYTABLE has been created as:
CREATE TABLE $db.mining.mytable
( A INTEGER, B INTEGER, C INTEGER, D CHAR(2),
E CHAR(2), F CHAR(2) );
The table MYTABLE has columns A, B, C, D, E, and F with related data. The columns A, B, and
C are type INTEGER, and columns D, E, and F are type CHAR.
FEDCBA
f1e1d1100101
f2e2d2200202
• Suppose that MYTABLE has only the first three columns: A, B, and C. The result of the
TRANSPOSE clause has three times as many rows (because there are three items in the
transpose item list) as there are rows in MYTABLE:
SELECT * FROM mytable
TRANSPOSE A, B, C AS VALCOL
KEY BY KEYCOL;
TRANSPOSE Clause 273