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 !
How are you Laurent?
I really enjoyed company and the discussions with you during OOW2006.
Have you ever seen the post of my collegae Anton? Maybe you still find them interesting…
Hope to seen you again in the near future.
The new pivot and unpivot keywords look very interesting and the syntax actually looks quite logical, although I’m sure I’ll still be reaching for the documentation everytime I need to use it!
Laurent, Where did you see this syntax ?is it 11g Beta (if you are on it, is there no NDA)?
thats pretty neat…is this in 11G