Columns to Rows

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
 



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-12032.html]SQL Snippets: Columns to Rows - UNION ALL Method[/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-12032.html">SQL Snippets: Columns to Rows - UNION ALL Method</a>

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

  • Link Text : SQL Snippets: Columns to Rows - UNION ALL Method
  • URL (href): http://www.sqlsnippets.com/en/topic-12032.html