• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Returning comma separated values in sqlplus

I have a  query that returns a resultset of IDs. Ideally I would like to return a single row of comma separated ids instead of a list. For example, Select ID from table returns
ID
1
2
3

I would like to return
ID
1,2,3

Is this possible?
0
atxman
Asked:
atxman
2 Solutions
 
slightwv (䄆 Netminder) Commented:
There are many ways.

Which way depends a lot on the maximum string length.

If it will be less than 4000 characters go with LISTAGG.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
0
 
sdstuberCommented:
select listagg(id,',') within group (order by id)
from your_table

or if a 11g or lower,  create your own aggregate
http://www.experts-exchange.com/articles/9391/How-to-Create-User-Defined-Aggregates-in-Oracle.html

that article also includes an example of using the COLLECT function with a wrapper to generate a delimited string without needing your own aggregate.

if you can't create a new object, then try xml aggregation

SELECT RTRIM(
           XMLQUERY(
               '//x/text()'
               PASSING XMLAGG(XMLELEMENT("x", id || ',') ORDER BY id)
               RETURNING CONTENT
           ),
           ','
       )
  FROM yourtable;

it's a more resource intensive operation than listagg, collect, or writing your own aggregate but will work in a pinch
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now