Oracle SQL: How to convert one column of Select to rows

Question

I am new to Oracle and am looking for a way to convert 1 column in a Select to rows.

My first approach was using Listagg which does exactly what I want but the character limit for this is not enough for my case. As an alternative I would like to do the following.

SELECT
   t.col1
   , t.col2
   , t.col3
   , t.col4
   , t.col5
FROM
   my table t

Instead of the standard output of t.col1 t.col2 t.col3 t.col4 t.col5 I would like t.col2 to appear in rows (i.e. below each other) instead of in columns (next to each other). Col2 always contains a value and each of them should appear in a separate row.

When searching for a solution to this I came across Unpivot and Decode but am not sure if and how this could be applied here.

Can someone tell me how this can be achieved ?

Many thanks in advance for any help,
Mike

Answer 1

A simple method -- if your data is not too large -- is just to use union all. Your description makes it sound like you want this:

select col1, col2, col5
from t
where col2 is not null
union all
select col1, col3, col5
from t
where col2 is not null
union all
select col1, col4, col5
from t
where col2 is not null;

Hmmm, or if you just want the distinct values in col2:

select distinct col2
from t;