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

score 7 · Answer 1 · answered Oct 30 '12 at 15:45

7

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

answered Oct 30 '12 at 15:45

Colin 't Hart

7,372
3
28
51

+1, and remember that listagg is limited by 4000 bytes. – Erkan Haspulat Oct 30 '12 at 15:51
1

Because it's a varchar, not a clob. Rumours are that in Oracle 12c -- due around the end of the year -- will have varchars up to 32767 in the database just like in PL/SQL... – Colin 't Hart Oct 30 '12 at 15:52
@ErkanHaspulat 4000 char, surely?! – Colin 't Hart Oct 30 '12 at 15:53
1

Thank you... Just one problem.. The output comes like this. Release _name for 123 is march,april,april... how to avoid duplicate april? – Jap Evans Oct 30 '12 at 15:56
1

You can `distinct` your data before aggregating it. You'll probably need a subquery or a with clause. – Colin 't Hart Oct 30 '12 at 16:09
@Colin'tHart Yes, and not 4000 chars 4000 bytes. Check this out: `select listagg('a', '') within group (order by 1) from all_objects where rownum < 4002` You get: `ORA-01489: result of string concatenation is too long` And with this: `select listagg('ğ', '') within group (order by 1) from all_objects where rownum < 2002` You get the same: `ORA-01489: result of string concatenation is too long` That's because 'ğ' is stored in 2 bytes, and 'a' is only 1 byte. – Erkan Haspulat Oct 31 '12 at 16:55

Taryn · Accepted Answer · 2012-10-30T16:17:53.023

6

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

edited Oct 30 '12 at 16:17

answered Oct 30 '12 at 16:06

Taryn

242,637
56
362
405

@Jeevan you are welcome, would can probably clean this up a bit using a `with` clause instead of the sub-select, whichever is your preference – Taryn Oct 30 '12 at 16:16
hmm ok.. i am not sure how to do that. (I'm new to oracle..brand new :)). I will go with your answer for now. – Jap Evans Oct 30 '12 at 16:23
need help pls.. i want to do the duplicate removal on both cycle_name and release_name columns.. i tried few things but does not seem to work. http://sqlfiddle.com/#!4/430ee/15 please help – Jap Evans Oct 30 '12 at 16:42
@Jeevan please check this demo -- http://sqlfiddle.com/#!4/430ee/22 – Taryn Oct 30 '12 at 16:46

score 2 · Answer 3 · answered Oct 30 '12 at 15:48

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.

score 0 · Answer 4 · answered Oct 30 '12 at 15:42

Through the WM_concat function (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:

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems The LISTAGG function, which can produce the same output as WM_CONCAT is both documented and supported by Oracle.

Oracle concatenation of columns with comma

4 Answers4