pivot table

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
pivot (max(value) 
for type in (
  'gender' as gender,
  'category' as category))
where person.id = property.person (+);

and with the unpivot keyword

select * 
from dept 
unpivot (value 
for type in (
  dname as 'DNAME',
  loc as 'LOC'));

It would be interesting to compare the execution plans !