HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Unpivot ETL transformation pattern - 10g vs. 11g
ETL PATTERNS
09-Sep-2008
Unpivot transformation (several columns into several rows) becomes much simplier with Oracle 11g. Lets see.

In former versions of Oracle we used following workaround to unpivot source table data:

--Oracle 10g
-------------
select * from(
select 
   b
  ,case bb.lvl when 1 then 'M0' when 2 then 'M1' when 3 then 'M2' when 4 then 'M3' end as metaindicator 
  ,case bb.lvl when 1 then M0 when 2 then M1 when 3 then M2 when 4 then M3 end as amount 
from tt6 aa
cross join (select level lvl from dual connect by level<=4) bb
) where amount is not null;

[Download]
See: Detail 10g workaround description

Following statement looks much simpler. Lets hope that its performance is as well as it looks written.

--Oracle 11g
-------------
select * from tt6 unpivot (amount for metaindicator in(M0,M1,M2,M3));

[Download]

B ME AMOUNT ---------- -- ---------- 100 M0 1000 28 M0 280 44 M0 440 76 M0 760 20 M0 200 72 M0 720 84 M0 840 88 M0 880 4 M0 40 24 M0 240 32 M0 320

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky