| Dynamic PIVOT [message #279059] |
Tue, 06 November 2007 14:55  |
hdogg
Messages: 94
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  |
 |
Kevin Meade
Messages: 2103
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 #279280 is a reply to message #279278] |
Wed, 07 November 2007 10:35 |
hdogg
Messages: 94
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 |
 |
Maaher
Messages: 7065
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
