Query to get column value comma separated

Question

Possible Duplicates:
Is there an Oracle SQL query that aggregates multiple rows into one row?
Agregate rows in Oracle SQL statement

I am working with Oracle 10g. I want to have a comma separated String from a column in a table.

e.g.

Table  : Customer        
Columns: id and name

Data:

  id-------name

   1-------John
   2-------Galt
   3-------Howard
   4-------Roark

Output of query should be Jon,Galt,Howard,Roark

I believe my requirement is much simpler than the question in the thread you mentioned. — Sid, Jan 31 '11 at 22:14
Here is another relevant question: http://stackoverflow.com/questions/1788011/transpose-select-results-with-oracle (and best answer: http://www.sqlsnippets.com/en/topic-11591.html ). — Zsolt Botykai, Jan 31 '11 at 22:14
I don't have any grouping to do, I just need the column value extracted to a String. Isn't there a simpler way? — Sid, Jan 31 '11 at 22:16
I believe it's actually a duplicate of http://stackoverflow.com/questions/2667237/agregate-rows-in-oracle-sql-statement — Justin Cave, Jan 31 '11 at 22:23
you have plenty of good answers in the comments, and yes, what you are asking to do is "grouping". If you wanted the sum of a set of values you'd group by and use sum(). You're asking for something similar but with strings. — Jim Garrison, Jan 31 '11 at 22:26

score 2 · Accepted Answer · answered Jan 31 '11 at 22:34

2

Ok, got it, all I wanted was this:

SELECT WM_CONCAT(NAME) FROM CUSTOMER;

Marking all comments as +1. Thanks guys.

answered Jan 31 '11 at 22:34

Sid

2

Be aware that WM_CONCAT is, technically, undocumented. And it requires that Workspace Manager is installed, I believe. – Justin Cave Jan 31 '11 at 22:41
2

We use this in production and it works as expected. One thing to note, if relevant at all, Crystal Reports does not like WM_CONCAT when calling it via SQL Expression, even if you're pointing at a Oracle 10 or greater database. – contactmatt Jan 31 '11 at 22:57

1 Answers1