How to convert row to column for below scenario

Question

iam trying to convert rows to columns in SQL. the below is my data.

id1  id2  id3
---------------
100  101  103

i want to populate output below. could you please help me out.

output:
-----
100
101
103

tried below:

select * from(select value, columnname from test_109)
src
pivot
(value for columnname in(id,id_1,id_2)
)piv;

Does this answer your question? [Oracle show columns as row](https://stackoverflow.com/questions/38504172/oracle-show-columns-as-row) — astentx, Mar 10 '22 at 10:15

score 0 · Answer 1 · answered Mar 10 '22 at 07:56

0

A simple option is

select id1 from test_109 union all
select id2 from test_109 union all
select id3 from test_109;

answered Mar 10 '22 at 07:56

Littlefoot

score 0 · Answer 2 · answered Mar 10 '22 at 10:04

You want UNPIVOT (not PIVOT):

SELECT value
FROM   test_109
UNPIVOT (value for columnname in(id1,id2,id3));

Which, for the sample data:

CREATE TABLE test_109 (id1, id2, id3) AS
SELECT 100, 101, 103 FROM DUAL;

Outputs:

VALUE

100

101

103

db<>fiddle here

2 Answers2