Oracle concatenation of columns with comma [duplicate]

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...

Answer 2

You will want to use LISTAGG() to perform this task. The other answers don't remove any of the duplicate values, to remove the duplicates, you can use something similar to this:

select c.efforts_id, 
  c.cycle_name,
  listagg(r.release_name, ', ') within group (order by c.efforts_id) as release_name
from
(
  select efforts_id,
    listagg(cycle_name, ', ') within group (order by efforts_id) as cycle_name
  from yourtable
  group by efforts_id
) c
inner join
(
  select distinct efforts_id, release_name
  from yourtable
) r
  on c.efforts_id = r.efforts_id
group by c.efforts_id, c.cycle_name

See SQL Fiddle with Demo

Answer 3

If you have Oracle 11g R2, then LISTAGG is the preferred way to do it:

SELECT efforts_id,
    LISTAGG(cycle_name) WITHIN GROUP(ORDER BY cycle_name),
    LISTAGG(release_name) WITHIN GROUP(ORDER BY cycle_name)
FROM MY_TABLE
GROUP BY efforts_id

If not, this article shows the alternative ways of doing it.