First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.
Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT
Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)
select person.name,
property.type,
property.value
from person, property
where
person.id=property.person;
NAME TYPE VALUE
---- -------- ------
John gender male
Mary category junior
Mary gender female
for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)
select name,
gender.value gender,
category.value category
from person,
property gender,
property category
where
person.id = gender.person(+)
and gender.type(+)='gender'
and person.id = category.person(+)
and category.type(+)='category';
NAME GENDER CATEGO
---- ------ ------
Mary female junior
John male
By using the Tom Kyte method described on asktom, I could have used aggregation.
select name,
max(decode(type,'gender',value)) gender,
max(decode(type,'category',value)) category
from person , property
where person.id = property.person (+)
group by name;
NAME GENDER CATEGO
---- ------ ------
John male
Mary female junior
To do the opposite, I posted once in a forum
select deptno,
decode(x,1,'DNAME','LOC') type,
decode(x,1,dname,loc) value
from dept,
(select 1 x from dual union all
select 2 from dual);
DEPTNO TYPE VALUE
---------- ----- --------------
10 DNAME ACCOUNTING
20 DNAME RESEARCH
30 DNAME SALES
40 DNAME OPERATIONS
10 LOC NEW YORK
20 LOC DALLAS
30 LOC CHICAGO
40 LOC BOSTON
Well, in the next generation database, this is going to be easier, maybe.
With the introduction of pivot keyword, the following should work
select name, type, value
from person , property
<b>pivot</b> (max(value)
for type in (
'gender' as gender,
'category' as category))
where person.id = property.person (+);
and with the unpivot keyword
select *
from dept
<b>unpivot</b> (value
for type in (
dname as 'DNAME',
loc as 'LOC'));
It would be interesting to compare the execution plans !
11g, Blogroll, sql
