Home » SQL & PL/SQL » SQL & PL/SQL » Pivot hints? (Oracle 10g)
Pivot hints? [message #337629] Thu, 31 July 2008 08:07 Go to next message
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 #337634 is a reply to message #337629] Thu, 31 July 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Clue:
SQL> select 1 attr from dual union all select 2 from dual;
      ATTR
----------
         1
         2

2 rows selected.

Just join this with your input and use decode.

Regards
Michel
Re: Pivot hints? [message #337655 is a reply to message #337629] Thu, 31 July 2008 09:03 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member



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 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Pivot hints? [message #338038 is a reply to message #337629] Sat, 02 August 2008 12:50 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you want to know about pivoting in SQL, read this:

Kevin Meade's OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Kevin
Previous Topic: COMPARISON OF CURRENT RECORD WITH PREVIOUS OR NEXT RECORD
Next Topic: email not delivering from pl/sql
Goto Forum:
  


Current Time: Wed Dec 07 22:28:30 CST 2016

Total time taken to generate the page: 0.05693 seconds