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

Question

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:

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?

Aleksej · Accepted Answer · 2016-09-27 08:37:25Z

6

You can use LISTAGG:

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

answered Sep 27, 2016 at 8:37

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related