CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Vijay
Vijay

Reputation: 67221

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?

Upvotes: 2

Views: 3728

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You can use LISTAGG:

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

Upvotes: 6

Related Questions