Concatenating Oracle Comma Columns

Question

Possible duplicate:
How to combine multiple lines in a comma separated list in Oracle?

Can someone 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 result:

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

I am new to oracle, so not sure how to do this. Any help would be appreciated.

thanks

Answer 1

For this task you will want to use LISTAGG() . Other answers do not remove one of the duplicate values, to remove 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 2

What you need is a "row aggregation". Tim Hall is a great site showing the alternatives that 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 the 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; 

Please note that using the wm_concat function is not supported by Oracle ...

Answer 3

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

 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 alternative ways of doing it.

Answer 4

Via WM_concat (and GROUP BY , of course)

 SELECT efforts_id, wm_concat(cycle_name), wm_concat(release_name) FROM MY_TABLE GROUP BY efforts_id 

Hmmm, just found this:

Please note that WM_CONCAT is undocumented and unsupported by Oracle, that is, it should not be used in production systems. The LISTAGG function, which can produce the same result as WM_CONCAT , is documented and maintained by Oracle.