Oracle concatenation of columns with comma

Question

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

Could some one please tell me how to achieve the following?

Table:

efforts_id        cycle_name      release_name 
123               quarter         march 
123               half            april 
123               full            april
124               quarter         may

My expected output:

efforts_id        cycle_name            release_name 
123               quarter,half,full     march,april
124               quarter               may

I am a beginner in oracle so not sure how to do this. Any help would be appreciated.

Thanks

Answer 1

What you need is "string aggregation". Tim Hall's excellent site shows the alternatives you have depending on the exact version of Oracle you have: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

In 11gR2 (current at time of writing), you should use the listagg function:

select
  efforts_id,
  listagg(cycle_name, ',') within group (order by cycle_name) as cycle_name,
  listagg(release_name, ',') within group (order by release_name) as release_name
from my_table
group by efforts_id;

Note that the use of the wm_concat function is unsupported by Oracle...