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