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