Transform SQL Rows into Comma-Separated values in Oracle

Question

Possible Duplicate:
How can I combine multiple rows into a comma-delimited list in Oracle?

How can you produce a comma-separated values from list of return rows in SQL without creating a function? Need to remove duplicates and null or with 'None' as the value.

Example: select name from student;

The result :

         NAME         
        ------
        Zed
        Charlo
        None
        Charlo
        Dionn
        Ansay

Desired output :

              Name
             -------
             Zed,Charlo,Dionn,Ansay

first hit: http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle — Mitch Wheat, Feb 01 '12 at 02:16
BTW: if you have any Oracle question, prefix your internet search with "Tom kyte" — Mitch Wheat, Feb 01 '12 at 02:23

score 21 · Accepted Answer · answered Feb 01 '12 at 02:46

21

http://sqlfiddle.com/#!4/9ad65/2

select 
  listagg(name, ',') 
    within group (order by id) as list 
from student

answered Feb 01 '12 at 02:46

Jake Feasel

16,785
5
53
66

5

Full disclosure: sqlfiddle.com is my site. – Jake Feasel Feb 01 '12 at 02:46
1

@JakeFeasel SQL Fiddle looks pretty neat! – Sathyajith Bhat Feb 01 '12 at 04:39
2

@Sathya thanks, I hope people here find it a useful tool for answering these sorts of questions. – Jake Feasel Feb 01 '12 at 05:01

Transform SQL Rows into Comma-Separated values in Oracle

1 Answers1

Related