Home » SQL & PL/SQL » SQL & PL/SQL » Transpose Multiple Rows into Columns
Transpose Multiple Rows into Columns [message #315668] Tue, 22 April 2008 06:19 Go to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Hi All,

I have multiple rows in a Table, I am trying to Query on these Rows in order to get a Single Record with the Row values fetched as separate Columns. The illustration below clarifies the situation:

 ID      TIME            FOR_KEY   TYPE    VALUE
 101    22-APR-08 19:31     501      1       5.622
 102    22-APR-08 19:31     501      3       60.59
 103    22-APR-08 19:31     501      5       37.24 


The Desired End Result of my Query should be like this:

  TIME             TYPE 1   TYPE 3    TYPE 5
22-APR-08 19:31     5.622    60.59     37.24


So the END Result is a SINGLE Record with the VALUES of Each Type in a Separate Column. I was Trying the Following Query but did not give me the correct result:
Select EVENT_TIME as Reading_Time,
DECODE(a.INSTRUMENT_TYPE_ID,1, a.INSTRUMENT_VALUE,0) as  TYPE_1, 
DECODE(a.INSTRUMENT_TYPE_ID,3, a.INSTRUMENT_VALUE,0) as TYPE_3, 
DECODE(a.INSTRUMENT_TYPE_ID,5, a.INSTRUMENT_VALUE,0) as TYPE_5  
from OMS_MGR.ATOMS_MOVEMENT_EVENT a
WHERE a.TAG  =  'Tank 05T130'
AND  a.EVENT_TIME  =  '2006-04-20 19:31:00'
AND a.INSTRUMENT_TYPE_ID IN (1,3,5)



READING_TIME	      TYPE_1   TYPE_3	TYPE_5
2006-04-20 19:31:00	5.622    0	  0
2006-04-20 19:31:00	0        0	  37.24
2006-04-20 19:31:00	0	 60.59	  0


Any help is appreciated.

Thanks,
Baz
Re: Transpose Multiple Rows into Columns [message #315682 is a reply to message #315668] Tue, 22 April 2008 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel

[Updated on: Tue, 22 April 2008 06:55]

Report message to a moderator

Re: Transpose Multiple Rows into Columns [message #315687 is a reply to message #315668] Tue, 22 April 2008 07:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

This is one of most common question asked in every single forum I have visited. You are almost doing the right thing but you are missing the trick which is the group by. Try with that also search in this forum or google it. Also, please try to follow what Michael has suggested.

Regards

Raj
Re: Transpose Multiple Rows into Columns [message #315697 is a reply to message #315668] Tue, 22 April 2008 08:04 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
bbaz wrote on Tue, 22 April 2008 07:19

AND a.EVENT_TIME = '2006-04-20 19:31:00'



Looks invalid to me.
Re: Transpose Multiple Rows into Columns [message #315713 is a reply to message #315668] Tue, 22 April 2008 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
With Oracle characters between single quote marks are STRINGS only & ever.
'This is a string 2008-04-22; and not a date'

When you want a DATE datatype, use the TO_DATE function!
icon14.gif  Re: Transpose Multiple Rows into Columns [message #315716 is a reply to message #315713] Tue, 22 April 2008 09:21 Go to previous messageGo to next message
bbaz
Messages: 138
Registered: April 2007
Senior Member
Thanks All for your suggestions.

I solved it with the GROUP BY and with MAX on the DECODES. Laughing
Thanks Rajaram
Cheers,
Baz
Re: Transpose Multiple Rows into Columns [message #315736 is a reply to message #315716] Tue, 22 April 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And do you still think it is an expert question?

Regards
Michel
Re: Transpose Multiple Rows into Columns [message #316132 is a reply to message #315668] Wed, 23 April 2008 19:20 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
http://www.orafaq.com/node/1871

try this
Previous Topic: VERY VERY COMPLEX QUERY
Next Topic: cost of balance items recently purchased
Goto Forum:
  


Current Time: Wed Dec 07 12:45:26 CST 2016

Total time taken to generate the page: 0.12167 seconds