Home » SQL & PL/SQL » SQL & PL/SQL » query rows as columns
query rows as columns [message #236306] Wed, 09 May 2007 09:27 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
TableA
ROWID
STATUS
FIELD1
FIELD2
FIELD3

Data:
1 L 20 30 40
1 P 40 10 30
1 C 70 30 50
1 L 10 50 50
1 P 70 30 70
1 C 90 20 90

Need to query and display this as

STATUS_C STATUS_P
ROWID FIELD1 FIELD2 FIELD3 FIELD1 FIELD2 FIELD3
1 70 30 50 40 10 30
2 70 30 50 40 10 30

Need to convert the rows to columns based on STATUS. Any help..

thank you.
Re: query rows as columns [message #236318 is a reply to message #236306] Wed, 09 May 2007 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be easier if you:
- Give your Oracle version
- Post formatted
- Say us (in words) how you go to the result from the 2 tables (?) you gave.
- What are data
- What are the columns
- Why there is no 20 or 90 in result
- ...

Regards
Michel
Re: query rows as columns [message #236354 is a reply to message #236318] Wed, 09 May 2007 13:07 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
ok..

How to convert rows into columns in oracle ?

TableA:
ROWID	STATUS	FIELD1	FIELD2	FIELD3
1	L	10	20	30
1	P	40	10	10
1	C	90	80	200
2	L	445	67	67
2	P	66	76	786
2	C	56	57	56


Need to query and display this as 
Query o/p:
ROWID	STATUS_L			STATUS_P			STATUS_C		
	FIELD1	FIELD2	FIELD3	FIELD1	FIELD2	FIELD3	FIELD1	FIELD2	FIELD3
1	10	20	30	40	10	10	90	80	200
2	445	67	67	66	76	786	56	57	56


Re: query rows as columns [message #236357 is a reply to message #236354] Wed, 09 May 2007 13:25 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

check this one
really an interesting thread with lot of solutions Razz

regards,
shanth

[Updated on: Wed, 09 May 2007 13:32]

Report message to a moderator

Re: query rows as columns [message #236358 is a reply to message #236354] Wed, 09 May 2007 13:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a standard pivot.
Search for this there are many exemples, here, on AskTom, on Google, on...

Regards
Michel
Previous Topic: Nth record
Next Topic: Insert into
Goto Forum:
  


Current Time: Sun Dec 04 08:30:38 CST 2016

Total time taken to generate the page: 0.11910 seconds