UNPIVOT (11g)
Transforming columns into rows is easily accomplished with the SELECT command's UNPIVOT clause, introduced in Oracle 11g. (For database versions earlier than 11g see the remaining topics in this section for alternative techniques.)
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 skip 1 duplicates
select key, source, val
from
t UNPIVOT INCLUDE NULLS
( VAL FOR( SOURCE ) IN
( C1 AS 'C1',
C2 AS 'C2',
C3 AS 'C3'
)
)
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
If we do not need the rows with NULL values from C1, C2, or C3 we can use an EXCLUDE NULLS clause to filter them out.
select key, source, val
from
t unpivot EXCLUDE NULLS
( val for( source ) in
( c1 as 'C1',
c2 as 'C2',
c3 as 'C3'
)
)
order by key, source ;
KEY SOURCE VAL
--- ------ -----
r1 C1 v1
r1 C2 v2
r1 C3 v3
r2 C1 v1
r2 C2 v2
r3 C1 v1
r3 C3 v3
r4 C1 v1
r5 C2 v2
r5 C3 v3
r6 C2 v2
r7 C3 v3
If no INCLUDE/EXCLUDE NULLS clause is specified the default behaviour is EXCLUDE NULLS.
select key, source, val
from
t UNPIVOT
( val for( source ) in
( c1 as 'C1',
c2 as 'C2',
c3 as 'C3'
)
)
order by key, source ;
KEY SOURCE VAL
--- ------ -----
r1 C1 v1
r1 C2 v2
r1 C3 v3
r2 C1 v1
r2 C2 v2
r3 C1 v1
r3 C3 v3
r4 C1 v1
r5 C2 v2
r5 C3 v3
r6 C2 v2
r7 C3 v3
Gotchas
If the value column datatypes are not all in the same group, e.g. numeric, character, date, an error will be raised.
select key, source, val
from
t unpivot
( val for( source ) in
( n1 as 'N1', -- N1 is a NUMBER column
c2 as 'C2', -- C2 is a VARCHAR2 column
c3 as 'C3' -- C3 is a VARCHAR2 column
)
)
order by key, source ;
c2 as 'C2', -- C2 is a VARCHAR2 column
*
ERROR at line 6:
ORA-01790: expression must have same datatype as
corresponding expression
UNION ALL Method
In Oracle 10g or earlier one technique for transforming columns into rows uses the UNION ALL set operator. (For Oracle 11g see UNPIVOT (11g).)Consider this data table.
select key, c1, c2, c3 from t order by key ; KEY C1 C2 C3--- ----- ----- -----r1 v1 v2 v3r2 v1 v2r3 v1 v3r4 v1r5 v2 v3r6 v2r7 v3r8 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 skip 1 duplicatesselect key, 'C1' as source, c1 as val from t union allselect key, 'C2' as source, c2 as val from t union allselect key, 'C3' as source, c3 as val from torder by key, source ; KEY SOURCE VAL--- ------ -----r1 C1 v1r1 C2 v2r1 C3 v3r2 C1 v1r2 C2 v2r2 C3r3 C1 v1r3 C2r3 C3 v3r4 C1 v1r4 C2r4 C3r5 C1r5 C2 v2r5 C3 v3r6 C1r6 C2 v2r6 C3r7 C1r7 C2r7 C3 v3r8 C1r8 C2r8 C3 If we do not need the rows with NULL values from C1, C2, or C3 we can use WHERE clauses to filter them out. select key, 'C1' as source, c1 as val from t where c1 is not null union allselect key, 'C2' as source, c2 as val from t where c2 is not null union allselect key, 'C3' as source, c3 as val from t where c3 is not nullorder by key, source ; KEY SOURCE VAL--- ------ -----r1 C1 v1r1 C2 v2r1 C3 v3r2 C1 v1r2 C2 v2r3 C1 v1r3 C3 v3r4 C1 v1r5 C2 v2r5 C3 v3r6 C2 v2r7 C3 v3 MODEL Method
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 v3r2 v1 v2r3 v1 v3r4 v1r5 v2 v3r6 v2r7 v3r8 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 1select key, source, valfrom tmodel 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 v1r1 C2 v2r1 C3 v3r2 C1 v1r2 C2 v2r2 C3r3 C1 v1r3 C2r3 C3 v3r4 C1 v1r4 C2r4 C3r5 C1r5 C2 v2r5 C3 v3r6 C1r6 C2 v2r6 C3r7 C1r7 C2r7 C3 v3r8 C1r8 C2r8 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 breakscolumn I format 99select key, I, SOURCE, VAL, c1, c2, c3from twhere 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, c3from twhere 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 v3r1 1 C1r1 2 C2r1 3 C3 select key, i, source, val, c1, c2, c3from twhere 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 v3r1 1 C1 v1r1 2 C2 v2r1 3 C3 v3 select key, i, source, valfrom twhere 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 v1r1 2 C2 v2r1 3 C3 v3Performance Comparison
The following tables show performance metrics for one run each of the three Column to Row techniques described in the preceeding tutorials.
Each run transforms 10 columns from an 800 row table into a 8,000 row result set. See the log file from these tests for more details.
The tests were performed using SQL*Plus against an Oracle 11g Enterprise Edition Release 11.1.0.6.0 database running on Oracle Enterprise Linux 5.
Statistics
The following table shows database statistics where values for one method differ by more than 100 from another method.
METRIC_NAME UNPIVOT UNION ALL Collection MODEL
------------------------------ ------------ ------------ ------------ ------------
Elapsed Time (1/100 sec) 30 32 32 43
table scan rows gotten 8,800 157,493 142,869 1,600
session uga memory max 123,452 123,452 123,452 246,904
bytes sent via SQL*Net to clie 127,123 118,454 118,719 127,935
session logical reads 135 755 641 72
consistent gets 83 703 588 20
consistent gets from cache 83 703 588 20
consistent gets from cache (fa 82 691 551 19
no work - consistent read gets 44 635 540 8
table scan blocks gotten 44 614 540 8
recursive calls 36 295 9 9
session uga memory 65,464 0 65,464 65,464
sorts (rows) 8,000 0 0 0
Latch Gets
The following table shows total latch gets for each method. UNPIVOT UNION ALL Collection MODEL------------ ------------ ------------ ------------ 2,150 3,771 2,463 1,863 Techniques that use a small number of latches scale better than techniques that use a large number of latchesWarning: Results on your own systems with your own data will differ from these results. Results will even differ from one set of test runs to the next on the same machine. Run your own tests and average the results from multiple runs before making performance decisions.
It's really excellent blog, I just share your blog because it's really nice. Just look at this MSBI Online Course
ReplyDelete