Home » SQL & PL/SQL » SQL & PL/SQL » Row value as column name (10g Express Edition,Windows XP)
Row value as column name [message #340815] Thu, 14 August 2008 08:38 Go to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
Please find the below queries
create table sample (dt date,amt number(10,2),CMNTS VARCHAR2(100));

insert into sample VALUES ('14-AUG-08',100,'XYZ');

insert into sample VALUES ('14-AUG-08',200,'ABC');

insert into sample VALUES ('14-AUG-08',300,'DEF');

insert into sample VALUES ('13-AUG-08',400,'GHI');

insert into sample VALUES ('13-AUG-08',500,'JKL');

insert into sample VALUES ('12-AUG-08',600,'MNO');

SELECT * FROM SAMPLE;

DT	        AMT	CMNTS

8/14/2008	100	XYZ
8/14/2008	200	ABC
8/14/2008	300	DEF
8/13/2008	600	MNO
8/13/2008	500	JKL
8/12/2008	400	GHI

I need the result like this. I searched alot but i could not find the suitable answer for me, if it is repeated question, please forgive me. I need the row values (Dates) as column name rather using decode statment and giving static column name like 'Day 1','Day 2'.

CMNTS    8/14/2008   8/13/2008   8/12/2008
XYZ      100  
ABC      200 
DEF      300        
MNO                  600
JKL                  500
GHI                              400 



Re: Row value as column name [message #340819 is a reply to message #340815] Thu, 14 August 2008 08:46 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
This query has been posted here so many times.

Search by "pivot"


Regards,
Oli
Re: Row value as column name [message #340824 is a reply to message #340819] Thu, 14 August 2008 09:04 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
Oli,
Thanks for reply, Even before posting i searched alot, as it is common requirement, what ever the thing i found , it deals with the column name being static one, i mean it is not taking the column name from the rows. So could kindly provide me query for this

Thanks & Regards,
V.Narayanan.
Re: Row value as column name [message #340825 is a reply to message #340824] Thu, 14 August 2008 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Olivia said I posted several solutions with dynamic column names here and on AskTom.

Regards
Michel

[Updated on: Thu, 14 August 2008 09:11]

Report message to a moderator

Re: Row value as column name [message #340834 is a reply to message #340825] Thu, 14 August 2008 09:34 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
Michel,

Sorry to disturb again and again, I am not able to make query construct for this. If you have time please help me.

Thanks & Regards,
V.Narayanan.
Re: Row value as column name [message #340839 is a reply to message #340834] Thu, 14 August 2008 09:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
You could at least try! But it seems that you did never try...
www.google.com > search by key word "Ask Tom Pivot" and it took just less than 1 min

and as I said this query has been asked atleast once in a month here.


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:766825833740



Regards,
Oli

[Updated on: Thu, 14 August 2008 09:55]

Report message to a moderator

Re: Row value as column name [message #340847 is a reply to message #340839] Thu, 14 August 2008 10:28 Go to previous messageGo to next message
narayanan.v
Messages: 29
Registered: September 2005
Junior Member
Please understand me, without trying out i never used to go for forums , what ever search i made, it is showing group by class, for my case it is not required. Here i need to show all the comments ir respective whether there is a entry for the given date or not, if i have not made entry for the given date, then it will be null for us in the following i o/p i put as X, One more thing CMNTS is not a fixed one, it can be any thing like 123,534, some thing like this and dates also can vary one time i can query for first 2 days (8/14,8/13) also. Sad

CMNTS    8/14/2008   8/13/2008   8/12/2008
XYZ      100         X            X
ABC      200         X            X 
DEF      300         X            X
MNO      X           600          X
JKL      X           500          X
GHI      X           X            400 
Re: Row value as column name [message #340848 is a reply to message #340847] Thu, 14 August 2008 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you go to link Olivia posted?
Obviously not as the answer is there.
I give one more clue: Ctl-F, "michel cadot"

Regards
Michel
Re: Row value as column name [message #350573 is a reply to message #340847] Thu, 25 September 2008 08:14 Go to previous messageGo to next message
r_vijay83
Messages: 2
Registered: September 2008
Junior Member
Hi,

I am facing the same problem of what you have faced.

Problem :

CMNTS 8/14/2008 8/13/2008 8/12/2008
XYZ 100 X X
ABC 200 X X
DEF 300 X X
MNO X 600 X
JKL X 500 X
GHI X X 400


Can you help me in this issue. Have you found out the query ?


Thanks in Advance,
Vijay.
Re: Row value as column name [message #350576 is a reply to message #350573] Thu, 25 September 2008 08:20 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I would hazard a guess that the same answer applies to you. Did you take the same class? The question is verbatim the same.
Re: Row value as column name [message #350584 is a reply to message #350573] Thu, 25 September 2008 08:44 Go to previous messageGo to next message
r_vijay83
Messages: 2
Registered: September 2008
Junior Member
sorry, i dont understand what you say. I am new to this forum. I have been facing this problem from yesterday.

so, please get me detailed answer for my query.




Thanks in Advance,
vijay.
Re: Row value as column name [message #350588 is a reply to message #350584] Thu, 25 September 2008 08:54 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read and try what has been said in this topic?
If no, do it.

Regards
Michel
Previous Topic: Data Pulling
Next Topic: What are function we can't use in PL/SQL
Goto Forum:
  


Current Time: Sat Dec 10 07:22:39 CST 2016

Total time taken to generate the page: 0.04744 seconds