Columns to Rows

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


 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12698.html]SQL Snippets: Columns to Rows - UNPIVOT (11g)[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12698.html">SQL Snippets: Columns to Rows - UNPIVOT (11g)</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Columns to Rows - UNPIVOT (11g)
  • URL (href): http://www.sqlsnippets.com/en/topic-12698.html