creating pivot tables

[tuarek]

Hi all,

Is there any article / white paper that you can suggest to me to
create a pivot table? I found Alexander Kuznetsov's article on IBM's
web site. But i need something deeper than that.

Pivot table's number of columns may vary depeding upon the input
table/query and if possible I would like to avoid row_number function
call.

Regards,

Mehmet

[Serge Rielau]

tuarek wrote:

Hi all,

Is there any article / white paper that you can suggest to me to
create a pivot table? I found Alexander Kuznetsov's article on IBM's
web site. But i need something deeper than that.

Pivot table's number of columns may vary depeding upon the input
table/query and if possible I would like to avoid row_number function
call.

<plug for my talk at The Hague IM Tech conference again)
To PIVOT use
MAX(CASE WHEN pivotcol = 1 THEN c1 END) AS P1,
MAX(CASE WHEN pivotcol = 2 THEN c2 END) AS P2
and so forth,
To UNPIVOT:

SELECT pivotcol, c1 FROM T, LATERAL(VALUES(1, T.c1), (2, T.c2), ...) AS
S(pivotcol, c1)

To generalize this use a stored procedure and dynamic SQL to glue
together the statements.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[lennart]

Serge Rielau wrote:
[...]

SELECT pivotcol, c1 FROM T, LATERAL(VALUES(1, T.c1), (2, T.c2), ...) AS
S(pivotcol, c1)

I think I have seen an explanation of LATERAL vs TABLE but I fail to
find it now. Is the above statement equal to:

SELECT pivotcol, c1 FROM T, TABLE(VALUES(1, T.c1), (2, T.c2), ...) AS
S(pivotcol, c1)

?

/Lennart

[...]

[Serge Rielau]

le*****@kommunicera.umea.se wrote:

Serge Rielau wrote:
[...]

SELECT pivotcol, c1 FROM T, LATERAL(VALUES(1, T.c1), (2, T.c2), ...) AS
S(pivotcol, c1)

I think I have seen an explanation of LATERAL vs TABLE but I fail to
find it now. Is the above statement equal to:

SELECT pivotcol, c1 FROM T, TABLE(VALUES(1, T.c1), (2, T.c2), ...) AS
S(pivotcol, c1)

Yes. LATERAL is the SQL Standard keyword.
We added LATERAL as a synonym for TABLE in DB2 V8.2 and in DB2 Viper you
will find that the TABLE keyword has been deemphasized to a foot note:
nested-table-expression:

|-+----------------------------------------+--(fullselect)-|correlation|--|
| (2) |
'-LATERAL--+--------------------------+-'
'-| cont-handler |--WITHIN-'

2. TABLE can be specified in place of LATERAL.

TABLE as a keyword for table functions is not affected by that
(different animal altogether and SQL Standard compliant).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[lennart]

Serge Rielau wrote:

[...]

Yes. LATERAL is the SQL Standard keyword.
We added LATERAL as a synonym for TABLE in DB2 V8.2 and in DB2 Viper you
will find that the TABLE keyword has been deemphasized to a foot note:
nested-table-expression:

Ah, I see. Time to unlearn table and learn lateral then :-)

Thanx a lot
/Lennart

[...]

[tuarek]

Serge,

Thanks for the pivot table help.

Is it possible to download slides/documents of Tampa and The Hague
events from IBM?

Regards,

Mehmet

[Serge Rielau]

tuarek wrote:

Serge,

Thanks for the pivot table help.

Is it possible to download slides/documents of Tampa and The Hague
events from IBM?

For Tampa you will be able to get them at the IDUG website.
For The Hague I'd imagine that IBM Learning services will make the
slides (and audio?) available. I'll try to find out. Send me an email so
I don't forget.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab