In Oracle 10g another technique for transforming columns into rows uses the SELECT command's MODEL clause (not available prior to 10g). If you are unfamiliar with the MODEL clause review the SQL Features Tutorials: MODEL Clause tutorial before proceeding. (For Oracle 11g see UNPIVOT (11g). For versions prior to 10g see UNION ALL Method or Collection Method)
Consider this data table.
select key, c1, c2, c3 from t order by key ;
KEY C1 C2 C3 --- ----- ----- ----- r1 v1 v2 v3 r2 v1 v2 r3 v1 v3 r4 v1 r5 v2 v3 r6 v2 r7 v3 r8
We can convert each row of C1, C2, and C3 values into three rows of values under a single column as follows.
break on key duplicates skip 1
select key, source, val
from t
model
return updated rows
partition by ( key )
dimension by ( 0 as i )
measures ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
rules upsert all
(
source[ 1 ] = 'C1' ,
source[ 2 ] = 'C2' ,
source[ 3 ] = 'C3' ,
val[ 1 ] = c1[ 0 ] ,
val[ 2 ] = c2[ 0 ] ,
val[ 3 ] = c3[ 0 ]
)
order by key, source ;
KEY SOURCE VAL --- ------ ----- r1 C1 v1 r1 C2 v2 r1 C3 v3 r2 C1 v1 r2 C2 v2 r2 C3 r3 C1 v1 r3 C2 r3 C3 v3 r4 C1 v1 r4 C2 r4 C3 r5 C1 r5 C2 v2 r5 C3 v3 r6 C1 r6 C2 v2 r6 C3 r7 C1 r7 C2 r7 C3 v3 r8 C1 r8 C2 r8 C3
Understanding How it Works
To understand how the solution works we will start with a simple MODEL query and build towards the final solution one step at a time.
clear breaks column I format 99 select key, I, SOURCE, VAL, c1, c2, c3 from t where key = 'r1' model partition by ( key ) dimension by ( 0 AS I ) measures ( 'xx' AS SOURCE, 'xxxxx' AS VAL, c1, c2, c3 ) rules () ;
KEY I SOURCE VAL C1 C2 C3 --- --- ------ ----- ----- ----- ----- r1 0 xx xxxxx v1 v2 v3
select key, i, source, val, c1, c2, c3
from t
where key = 'r1'
model
partition by ( key )
dimension by ( 0 as i )
measures ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
RULES UPSERT ALL
(
SOURCE[ 1 ] = 'C1' ,
SOURCE[ 2 ] = 'C2' ,
SOURCE[ 3 ] = 'C3'
)
ORDER BY I ;
KEY I SOURCE VAL C1 C2 C3 --- --- ------ ----- ----- ----- ----- r1 0 xx xxxxx v1 v2 v3 r1 1 C1 r1 2 C2 r1 3 C3
select key, i, source, val, c1, c2, c3
from t
where key = 'r1'
model
partition by ( key )
dimension by ( 0 as i )
measures ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
rules upsert all
(
source[ 1 ] = 'C1' ,
source[ 2 ] = 'C2' ,
source[ 3 ] = 'C3' ,
VAL[ 1 ] = C1[ 0 ] ,
VAL[ 2 ] = C2[ 0 ] ,
VAL[ 3 ] = C3[ 0 ]
)
order by i ;
KEY I SOURCE VAL C1 C2 C3 --- --- ------ ----- ----- ----- ----- r1 0 xx xxxxx v1 v2 v3 r1 1 C1 v1 r1 2 C2 v2 r1 3 C3 v3
select key, i, source, val
from t
where key = 'r1'
model
RETURN UPDATED ROWS
partition by ( key )
dimension by ( 0 as i )
measures ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
rules upsert all
(
source[ 1 ] = 'C1' ,
source[ 2 ] = 'C2' ,
source[ 3 ] = 'C3' ,
val[ 1 ] = c1[ 0 ] ,
val[ 2 ] = c2[ 0 ] ,
val[ 3 ] = c3[ 0 ]
)
order by i ;
KEY I SOURCE VAL --- --- ------ ----- r1 1 C1 v1 r1 2 C2 v2 r1 3 C3 v3