Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic PIVOT
Dynamic PIVOT [message #279059] Tue, 06 November 2007 14:55 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
The infamous PIVOT question:

The column names will be the date, which continually changes... hence i can't hard code in the SQL the dates since they are dynamic.

What needs to be done is select the distinct PERIODS and make them columns.

*Oracle 10g does not have the new PIVOT function.

Here is my Data:

JOB   PERIOD    AMOUNT
06111 31-OCT-07   123
07555 30-SEP-07   12
07555 28-FEB-08   47
04444 28-FEB-08   222


I need to PIVOT TO:
JOB  31-OCT-07 30-SEP-07 28-FEB-08
06111  123
07555               12       47
04444                        222
Re: Dynamic PIVOT [message #279078 is a reply to message #279059] Tue, 06 November 2007 19:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
23 views of your questions and still no reply yet, hmmm...

well, I offer two things off hand:

1) I wrote an article about pivots in sql and posted it on OraFaq, please read it for background.

Example of Data Pivots in SQL (rows to columns and columns to rows)

http://www.orafaq.com/node/1871

2) Might I suggest using dynamic sql in some form to create the query you need. The issue with column names is you don't know the column values till after you have look at the data. One possibility is of course to pass the data twice, once to get the column values, and then a second time to execute a query biuilt using the values you spyed as column aliases. As for how you report it out, that depends I suppose on the tools you are using. In sqlplus, maybe try a refcursor, or sql script files. Indeed, refcursor would work is just about any environment.

Good luck, Kevin
Re: Dynamic PIVOT [message #279116 is a reply to message #279059] Wed, 07 November 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

23 views of your questions and still no reply yet, hmmm...

Maybe because the question was already asked "several" times and we are tired to repeat that a search will give the answer. Sad

Regards
Michel
Re: Dynamic PIVOT [message #279278 is a reply to message #279116] Wed, 07 November 2007 10:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
that would explain it, thanks.

Kevin
Re: Dynamic PIVOT [message #279280 is a reply to message #279278] Wed, 07 November 2007 10:35 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
I have searched and searched and haven't been able to find that answer... it's dynamic... the other solutions are for predefined fields
Re: Dynamic PIVOT [message #279396 is a reply to message #279280] Thu, 08 November 2007 01:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What do you mean with "dynamic"? SQL needs to know what columns you select. In advance. Besides, how would you handle it on the front end?

Of course there are ways: a ref cursor, a script that constructs the SQL statement for you, ...

MHE

[Updated on: Thu, 08 November 2007 01:39]

Report message to a moderator

Re: Dynamic PIVOT [message #279401 is a reply to message #279280] Thu, 08 November 2007 02:01 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=15156&SkipA=0
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#51542758465483
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:52266643928180#52284733269998
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740#67646678097830
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740#75611182455045

Regards
Michel

[Updated on: Thu, 08 November 2007 02:45]

Report message to a moderator

Previous Topic: Help in refing the Sql..
Next Topic: Insert much slower in PL/SQL than native SQL
Goto Forum:
  


Current Time: Thu Dec 08 18:45:35 CST 2016

Total time taken to generate the page: 0.14680 seconds