473,326 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
6 13557
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Alan | last post by:
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have...
4
by: Del | last post by:
I need to create Pivot table in Excel from Access. Currently I run a query and output the data to an excel worksheet and create the pivots via automation. The issue I face is that the query may...
2
by: Rob | last post by:
I'm just getting around to using pivot tables and charts. I find the Pivot table interface to be INCREDIBLY frustrating. When I view a table in Design view, then choose Pivot table view, I get...
1
by: Johnny Meredith | last post by:
Dear All, I have an Access database that tracks the progress of income tax audits. When the taxing authorities make a change (an "Adjustment"), I record the pertinent information in the...
1
by: narina | last post by:
Hello, I have a problem with creating a pivot table in Excel from Access VBA. I have wrote something like this: ..... Dim objExcel As Object dim vArkusz as String Set objExcel = GetObject(,...
0
by: Rami | last post by:
Has any body tried using pivot tables in C# I am trying to achieve pivot table functionality ( Rendering Row fields / Column Fields / Data fields ) in C# with Excel 2003. I have complete data in one...
0
by: Zlatko Matić | last post by:
I have experienced some problems with total operations (sum, min, max, avg etc) in pivot tables nad pivot charts in .mde. In .mdb I can activate any totals operation. on both notebook and desktop...
3
by: Thyag | last post by:
Hi All, I need to group multiple tables in to a pivot. Could some body help me. Thanks in Advance, Thyag
1
by: mld01s | last post by:
I really need help!!! I dont know if its possible to share pivot tables, or see pivot tables in other machines that the one where the tables were created. This is what happens: I created a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.