Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Style Query (Oralce 11g)
Pivot Style Query [message #440630] Tue, 26 January 2010 06:32 Go to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi

I have a dataset which looks like this:

select 'accept.png' res	,'Colour' property	,'Red' value    from dual union all
select 'add.png'	,'Colour'	,'Red'                          from dual union all
select 'X.png'		,'Colour'	,'Blue'                         from dual union all
select 'X_accept.png'	,'Colour'	,'Blue'                         from dual union all
select 'X_add.png'	,'Colour'	,'Blue'                         from dual union all
select 'accept.png'	,'Dimension'	,'16x16'                        from dual union all
select 'add.png'	,'Dimension'	,'24x24'                        from dual union all
select 'X.png'		,'Dimension'	,'16x16'                        from dual union all
select 'X_accept.png'	,'Dimension'	,'16x16'                        from dual union all
select 'X_add.png'	,'Dimension'	,'32x32'                        from dual union all
select 'accept.png'	,'License'	,'GPL'                          from dual union all
select 'X.png'		,'License'	,'LGPL'                         from dual union all
select 'X_add.png'	,'License'	,'GPL'                          from dual 




And I want to transform it so that it looks like this


============================================================
Property       accept.png      add.png    ....    x_add.png
============================================================
Colour           Red            Blue                Blue
Dimension        16x16          24x24               32x132
License          GPL            LGPL                GPL
============================================================




I am aware of the pivot feature, but i don't think this is the correct technique to use in this situation since I don't want to aggregate them in any way.

Does anyone know what the correct transformation would be to transpose the above data.

Thanks in advance,
Vackar
Re: Pivot Style Query [message #440634 is a reply to message #440630] Tue, 26 January 2010 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am aware of the pivot feature, but i don't think this is the correct technique to use in this situation since I don't want to aggregate them in any way.

See the folowing topic, it will give different ways to do it:
http://www.orafaq.com/forum/t/154231/102589/

Regards
Michel
Re: Pivot Style Query [message #440657 is a reply to message #440630] Tue, 26 January 2010 09:09 Go to previous message
Vackar
Messages: 81
Registered: October 2007
Member
Thanks Michel,

My solution was as follows
select property, 
    max (decode(rk,1,value)) "accept.png" , 
    max (decode(rk,2,value)) "add.png", 
    max (decode(rk,3,value)) "application.png",
    max (decode(rk,4,value)) "application_accept.png", 
    max (decode(rk,5,value)) "application_add.png" 
from 
    (select property, res, value, dense_rank() over (order by res) rk from base) 
group by 
    property 
order 
    by property


Previous Topic: The IN function
Next Topic: Pivot Table
Goto Forum:
  


Current Time: Thu Sep 29 20:59:07 CDT 2016

Total time taken to generate the page: 0.19212 seconds