Home » SQL & PL/SQL » SQL & PL/SQL » Pivot Style Query (Oralce 11g)
Pivot Style Query [message #440630] |
Tue, 26 January 2010 06:32  |
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 #440657 is a reply to message #440630] |
Tue, 26 January 2010 09:09  |
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
|
|
|
Goto Forum:
Current Time: Thu Feb 13 03:18:34 CST 2025
|