Home » SQL & PL/SQL » SQL & PL/SQL » Row as Column
Row as Column [message #233363] Wed, 25 April 2007 17:06 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I want to write sql to get output for some of the row as column?
Some of the information is repeating except name so i want to make name as row but i don't know the value of name as it will come through web dynamically. IF i know the value then i know to use as i have pasted at bottom.
==============
[B]Output will be[/B]
==============
A.id 	B.id 	D.fname	d.lname	c.name
1	b1	pora	Sch	ACT1
1	b1	pora	Sch	ACT2

simple query with join like:

SELECT b.id, d.fname, d.lname, c.name  
FROM tab1 a,  tab_info b, tab3 c, tab_user d  
WHERE a.id = b.id 
  and a.id = d.id  
==================
MAX(DECODE(type, 'X' ,value, '')) "X",
MAX(DECODE(type, 'Y' ,value, '')) "Y",
But I can't use this as i don't know the value.

Thanks,
Re: Row as Column [message #233404 is a reply to message #233363] Thu, 26 April 2007 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good to give us the output, but it will be better to also give us the inputs... and the Oracle version.
And when you use formatting, break your lines so we don't have to right scroll to get the end of your sentences or find the buttons.

Regards
Michel
Re: Row as Column [message #233546 is a reply to message #233404] Thu, 26 April 2007 09:21 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, Sorry about it.
I have ORacle 9i R2.

From this kind of normal output, i want to make records like:

A.id 	B.id 	D.fname	d.lname	c.name
1	b1	pora	Sch	ACT1
1	b1	pora	Sch	ACT2


Final results
==============
A.id 	B.id 	D.fname	d.lname	c.name_ACT1 	c.name_ACT2 (This name column value will be dynamic)
1	b1	pora	Sch	    Yes (or 1)	  Yes (or 0 if not that vlaue)

Thanks in advance.

Re: Row as Column [message #233555 is a reply to message #233546] Thu, 26 April 2007 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still don't have the description of the inputs table and their data.

Regards
Michel
Re: Row as Column [message #233600 is a reply to message #233555] Thu, 26 April 2007 15:36 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Sorry about it. Please see the attached file and let me know if you have any question.
File contains table, insert script and output with query.

Thanks
Re: Row as Column [message #233645 is a reply to message #233600] Fri, 27 April 2007 01:34 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now it is clear.
Have a look at my answer on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#51542758465483

Regards
Michel
Previous Topic: sql null
Next Topic: list stored procedures
Goto Forum:
  


Current Time: Sat Dec 03 08:19:37 CST 2016

Total time taken to generate the page: 0.05775 seconds