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

SQL/MX Clauses

HP NonStop SQL/MX Reference Manual—540440-003

7-29

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) );

Within MXCI, the ANSI alias name has been mapped as:

CREATE SQLMP ALIAS db.mining.mytable $db.mining.mytable;

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.

•

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;

The result table of the TRANSPOSE query is:

AB C DEF

1 10 100 d1 e1 f1

2 20 200 d2 e2 f2

A B C D E F KEYCOL VALCOL

1 10 100 d1 e1 f1 1 1

1 10 100 d1 e1 f1 2 10

1 10 100 d1 e1 f1 3 100

2 20 200 d2 e2 f2 1 2

2 20 200 d2 e2 f2 2 20

2 20 200 d2 e2 f2 3 200