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 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, 'C1' as source, c1 as val from t union all
select key, 'C2' as source, c2 as val from t union all
select key, 'C3' as source, c3 as val from t
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 WHERE clauses to filter them out. select key, 'C1' as source, c1 as val from t where c1 is not null union all
select key, 'C2' as source, c2 as val from t where c2 is not null union all
select key, 'C3' as source, c3 as val from t where c3 is not null
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
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 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
Performance 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