467,189 Members | 1,237 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

creating pivot tables

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
  • viewed: 12936
Share:
6 Replies
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

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

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
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
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.

Similar topics

4 posts views Thread by Del | last post: by
2 posts views Thread by Rob | last post: by
reply views Thread by Rami | last post: by
3 posts views Thread by Thyag | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.