Pivot hints? [message #337629] |
Thu, 31 July 2008 08:07  |
phil_mcl
Messages: 1 Registered: July 2008
|
Junior Member |
|
|
Hi
I have a query which returns a result like this:
Country Type ATTR_1_PREV ATTR_1 ATTR_2_PREV ATTR_2
UK PRP 0.1 0.2 0.5 0.6
UK POP 0.3 0.4 0.7 0.8
The _PREV columns are calculated in the query with the analytic LAG function, the other columns are real attributes in the database.
I would like to pivot some of the data, like this:
Column Country Type Prev Curr
ATTR_1 UK PRP 0.1 0.2
ATTR_1 UK POP 0.3 0.4
ATTR_2 UK PRP 0.5 0.6
ATTR_2 UK POP 0.7 0.8
I'm working with Oracle 10G so I think there are no pivot() functions, and I'm only equipped with basic pl/sql knowhow...
Any hints or ideas what's the best way to achieve such a pivot?
Thanks a lot,
Phil
|
|
|
|
Re: Pivot hints? [message #337655 is a reply to message #337629] |
Thu, 31 July 2008 09:03   |
|
Try It,
select a.Country,a.Type,a.Prev,a.Curr
From
(
(select Country,Type,ATTR_1_PREV prev,ATTR_1 curr
from Tablename)
Union
(select Country,Type,ATTR_1_PREV prev,ATTR_1 curr
from tablename)
)
a;
|
|
|
Re: Pivot hints? [message #337662 is a reply to message #337655] |
Thu, 31 July 2008 09:37   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
cvs_1984 wrote on Thu, 31 July 2008 16:03 | Try It,
|
Did YOU?
What's the difference (except for filtering dupes) between
select * from a
and
select * from
(select * from a
union
select * from a)
[Updated on: Thu, 31 July 2008 09:38] Report message to a moderator
|
|
|
Re: Pivot hints? [message #337698 is a reply to message #337662] |
Thu, 31 July 2008 13:34   |
|
select a.Country,a.Type,a.Prev,a.Curr
From
(
(select Country,Type,ATTR_1_PREV prev,ATTR_1 curr
from Tablename)
Union
(select Country,Type,ATTR_2_PREV prev,ATTR_2 curr
from tablename)
)
a;
|
|
|
Re: Pivot hints? [message #337700 is a reply to message #337698] |
Thu, 31 July 2008 13:56   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Weak solution, you have to scan the table twice.
In addition, I think OP wants ALL results, so you should use UNION ALL instead of UNION in your solution which is I repeat not the best one.
Also why do you use an inline view? What is the purpose of the outer query?
Regards
Michel
[Updated on: Thu, 31 July 2008 13:58] Report message to a moderator
|
|
|
|