OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Pivot hints? (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Pivot hints? [message #337634 is a reply to message #337629]

Thu, 31 July 2008 08:17

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Pivot hints? [message #337655 is a reply to message #337629]

Thu, 31 July 2008 09:03

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;

Report message to a moderator

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

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;

Report message to a moderator

Re: Pivot hints? [message #337700 is a reply to message #337698]

Thu, 31 July 2008 13:56

Michel Cadot
Messages: 68770
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

Re: Pivot hints? [message #338038 is a reply to message #337629]

Sat, 02 August 2008 12:50

Kevin Meade
Messages: 2103
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 04 04:12:00 CST 2025