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

Can you provide sample data and desired results? – Gordon Linoff Jun 18 '17 at 13:59 — Gordon Linoff, Jun 18 '17 at 13:59

score 1 · Accepted Answer · answered Jun 18 '17 at 14:00

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;

answered Jun 18 '17 at 14:00

Gordon Linoff

1,242,037
58
646
786

Thanks, Gordon. I just saw that I can simply concatenate two columns with a double pipe and add a line break between them and select the rest as normal which works here. However, I will accept this answer as it is very useful too. Thanks again. – keewee279 Jun 18 '17 at 14:16

score 0 · Answer 2 · answered Jun 18 '17 at 18:28

You are looking for the UNPIVOT function

SELECT col
FROM my table t
UNPIVOT INCLUDE NULLS (col FOR source_column_name IN (col1, col2, col3, col4, col5)

COL
----
Value 1
Value 2
Value 3
Value 4
Value 5

The result contains five rows with one column COL each that contains the value from the columns COL1 to COL5. The (unused) column SOURCE_COLUMN_NAME contains the name of the column where the data is coming from. You can remove the INCLUDING NULLS if you are only interested in rows the COL IS NOT NULL.

See the ORACLE-BASE article on PIVOT and UNPIVOT operators for more details.

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

2 Answers2