| Transpose Multiple Rows into Columns [message #315668] |
Tue, 22 April 2008 06:19  |
bbaz
Messages: 133
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  |
Michel Cadot
Messages: 15244
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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]
|
|
|
| Re: Transpose Multiple Rows into Columns [message #315687 is a reply to message #315668 ] |
Tue, 22 April 2008 07:27 |
S.Rajaram
Messages: 516
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 |
joy_division
Messages: 1978
Registered: February 2005
Location: NY |
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 |
anacedent
Messages: 5027
Registered: July 2005
Location: surf meets turf in 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!
|
|
|
|
| Re: Transpose Multiple Rows into Columns [message #315736 is a reply to message #315716 ] |
Tue, 22 April 2008 10:30 |
Michel Cadot
Messages: 15244
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
And do you still think it is an expert question?
Regards
Michel
|
|
|
|
]
Members
Search
Help
Register
Login
Home
Re: Transpose Multiple Rows into Columns