How to convert row to column for below scenario-CodePudding

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;

CodePudding user response：

A simple option is

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

CodePudding user response：

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