HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Unpivot in the Oracle 10g
ORACLE STUFFS
20-Apr-2009
Unpivot operation available in Oracle 11g raised wave of interest about the same functionality in the 10g version of Oracle. Unfortunately there is just a workaround available.
See: see Oracle 11g comparison

Unpivot operation causes several rows in target based on one row in the source. So you have to solve the task using following steps:

  • Multiply records.
  • Translate target values for each variant of multiplication.


Multiplicators:
the simplest way is to cross join table with the same number of columns as unpivot multiplication factor is. So if you create values based on two columns, the factor is 2. You can use a referral table, if it is. If it is not, you can use dummy multiplicators.
"Connect by" trick:

Select level from dual connect by level<=10;

This select returns 10 rows with values starting with 1 and ending with 10. Currently it seams it is the best known dummy multiplicator.


"Cube" trick

select rownum as lvl from (select 1 from dual group by cube(2,4,8,16)) where rownum<=10;

select rownum as lvl from (select 1 from dual group by cube(2,4,8,16,32)) where rownum<=30;

select rownum as lvl from (select 1 from dual group by cube(2,4,8,16,32,64)) where rownum<=50;

These 3 variants return various numbers of rows. This method is not as elegant as the previous one because you have to watch the CUBE list to satisfy enough variants. Anyway, it could be more efficient to get huger dummy resultsets (e.g. thousands). It is not usual for the unpivot operation so we mention it just for completeness).

SQL select rownum as lvl from (select 1 from dual group by cube(2,4,8,16)) where rownum LVL ---------- 1 2 3 4 5 6 7 8 9 10


Metaindicator
Metaindicator is a column indicating type of value in a record. So if you unpivot table with an unique key ID, the target table's key will consists of ID and METAINDICATOR.
Metaindicator should be derrived from the Multiplicator value:

case bb.lvl when 1 then 'M0' when 2 then 'M1' when 3 then 'M2' when 4 then 'M3' end as metaindicator  


Unpivoted Value
The value is derrived from several columns (the same as a muoltplication factor is).
One value is chosen based on a result of a Multiplicator:

case bb.lvl when 1 then M0 when 2 then M1 when 3 then M2 when 4 then M3 end as amount


Unpivot
Final unpivot transformation will look following way:

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; 

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