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