Vijay Vijay - 2 months ago 6
SQL Question

Convert a single column values into a single row in sql oracle

I have created a simple table which holds numbers as below:

CREATE TABLE saleshist (q1 NUMBER);
INSERT INTO saleshist VALUES (100);
INSERT INTO saleshist VALUES (101);
INSERT INTO saleshist VALUES (102);
INSERT INTO saleshist VALUES (103);

select * from saleshist


The above select shows a single column like:

100
102
102
103


I wish to see the output like below:

100,101,102,103


Yes its a comma separated column value in a single row as a single value.
This is just an example that I had in mind and column values can be many.
I guess UNPIVOT can do this But I am not able to understand that properly.
The best way I think of a solution to this is write a perl script and execute the query and then convert rows to columns with a comma separation.
Is there a direct way to do this in sql itself?

Answer

You can use LISTAGG:

select listagg(q1, ',') within group (order by null) from saleshist
Comments