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

Apr 18 '06 #1

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

Apr 18 '06 #2

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

[...]

Apr 18 '06 #3

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

Apr 18 '06 #4

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

[...]

Apr 18 '06 #5

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

Apr 18 '06 #6

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

Apr 18 '06 #7