Oracle sql: Convert column to row

Question

I have select statement like this.

select 'A','B','C' from dual;

It display

'A' 'B' 'C'
 A   B   C

I want it show like this

'A'

 A

 B

 C

Please help me.

Answer 1

Simply concatenate it with CHR(10) i.e. a line feed.

For example,

SQL> WITH DATA(a, b, c) AS(
  2  SELECT 'A','B','C' FROM dual
  3  )
  4  SELECT a||chr(10)||b||chr(10)||c text FROM DATA;

TEXT
-----
A
B
C


SQL>

Or, you could use UNION ALL.

SQL> column text format a4
SQL> WITH DATA(a, b, c) AS(
  2  SELECT 'A','B','C' FROM dual
  3  )
  4  SELECT a text FROM DATA UNION ALL
  5  SELECT b text FROM DATA UNION ALL
  6  SELECT c text FROM DATA;

TEXT
----
A
B
C

SQL>