You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




15 November 2006

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 !

4 Comments:

Anonymous John Scott said...

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!

15/11/06 14:50  
Anonymous ghassan salem said...

Laurent, Where did you see this syntax ?is it 11g Beta (if you are on it, is there no NDA)?

15/11/06 18:20  
Anonymous Marco Gralike said...

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...

http://technology.amis.nl/blog/?p=1197
http://technology.amis.nl/blog/?p=1207

Hope to seen you again in the near future.

Marco

15/11/06 19:20  
Blogger Vidya Balasubramanian said...

thats pretty neat...is this in 11G

15/11/06 20:25  

Post a Comment

<< Home