Home » SQL & PL/SQL » SQL & PL/SQL » column value as column name
column value as column name [message #208659] Mon, 11 December 2006 18:02 Go to next message
swamy99
Messages: 30
Registered: June 2006
Member
I need to get the column value as column name for pivot query. For a single id, you may have different dates and for every date there is a different amount.
ex: ID rdate amount
1 20060101 100.00
1 20060102 120.00
1 20060103 130.00
2 20060201 200.00
2 20060202 210.00
3 20060102 300.00
3 20060105 350.00
...................

The result should be like

ID 20060101 20060102 20060103 20060104 20060105 .........
1 100.00 120.00 130.00
2 200.00 210.00
3 300.00 350.00
...........
............
............

So the RDATE should be the column name here. Since the RDATE starts 20060101 and goes all the way to the end of the year, there will be 365 columns in that row. So it is not just the 4 or 5 columns that I put in the example, it grows to 365 columns in that row.
Any ideas?

Thanks

[Updated on: Mon, 11 December 2006 18:45]

Report message to a moderator

Re: column value as column name [message #208660 is a reply to message #208659] Mon, 11 December 2006 19:03 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://technology.amis.nl/blog/?p=300
Re: column value as column name [message #208661 is a reply to message #208660] Mon, 11 December 2006 19:11 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
If it is a week I can create without any problem, but the dates that are coming from the table has some missing dates which does not show up as a column, hence the date that is there in the table needs to be the column name.

Is it possible to append the column value as alias?
The row heading should look like
ID 20060101 20060102 20060103 .......

[Updated on: Mon, 11 December 2006 19:59]

Report message to a moderator

Re: column value as column name [message #208689 is a reply to message #208661] Mon, 11 December 2006 22:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
column My_Date new_val The_Date noprint
select sysdate My_Date from dual;

column Answer format a9 heading &The_Date
select 'Y/N' Answer from dual;

23-MAY-01
---------
Y/N

[Updated on: Mon, 11 December 2006 22:05]

Report message to a moderator

Re: column value as column name [message #208741 is a reply to message #208689] Tue, 12 December 2006 01:05 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
I am afraid it is not the right solution that I am looking for. Actually, the rdate data from the table (with 365 days approx) is supposed to be the column name.

id rdate
-- --------
1 20060101
1 20060102
1 20060103
......
2 20060201
2 20060202
........
3 20060301
3 20060302
3 20060303
3 20060304
.......

The column heading should look like

ID 20060101 20060102 20060103 .... 20060201 20060202 ..... 20060301 20060302 ......

Hope the above information is clear.

Re: column value as column name [message #209168 is a reply to message #208741] Wed, 13 December 2006 10:13 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member
may be this link help you
http://thinkoracle.blogspot.com/2006/04/pivot-queries-using-variable-number-of.html


Previous Topic: Oracle 9i client installation failed
Next Topic: Hierarcial retrieval - Connect By, Prior Not working
Goto Forum:
  


Current Time: Fri Dec 09 15:27:45 CST 2016

Total time taken to generate the page: 0.13989 seconds