By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

creating pivot tables

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a

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

P: n/a
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

P: n/a

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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.