How to use Oracle's LISTAGG function with a unique filter? [duplicate]

Question

I have a table like this:

group_id  name  
--------  ----
1         David
1         John
1         Alan
1         David
2         Julie
2         Charles

And I want the following result:

group_id  names
--------  -----
1         'Alan, David, John'
2         'Charles, Julie'

I can use the following query:

select group_id, 
       listagg(name, ',') within group (order by name) as names
from demotable
group by group_id 

To get this (very similar result):

group_id  names
--------  -----
1         'Alan, David, David, John'
2         'Charles, Julie'

Any ideas how I can filter the names by uniqueness in the LISTAGG call?

Answer 1

I don't have an 11g instance available today but could you not use:

SELECT group_id,
       LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names
  FROM (
       SELECT UNIQUE
              group_id,
              name
         FROM demotable
       )
 GROUP BY group_id

Answer 2

Super simple answer - solved!

my full answer here it is now built in in some oracle versions.

select group_id, 
regexp_replace(
    listagg(name, ',') within group (order by name)
    ,'([^,]+)(,\1)*(,|$)', '\1\3')
from demotable
group by group_id;  

This only works if you specify the delimiter to ',' not ', ' ie works only for no spaces after the comma. If you want spaces after the comma - here is a example how.

select 
replace(
    regexp_replace(
     regexp_replace('BBall, BBall, BBall, Football, Ice Hockey ',',\s*',',')            
    ,'([^,]+)(,\1)*(,|$)', '\1\3')
,',',', ') 
from dual

gives BBall, Football, Ice Hockey

Answer 3

create table demotable(group_id number, name varchar2(100));
insert into demotable values(1,'David');
insert into demotable values(1,'John');
insert into demotable values(1,'Alan');
insert into demotable values(1,'David');
insert into demotable values(2,'Julie');
insert into demotable values(2,'Charles');
commit;

select group_id, 
       (select listagg(column_value, ',') within group (order by column_value) from table(coll_names)) as names
from (
  select group_id, collect(distinct name) as coll_names 
    from demotable
    group by group_id 
)

GROUP_ID    NAMES
1   Alan,David,John
2   Charles,Julie

Answer 4

select group_id, 
       listagg(name, ',') within group (order by name) as names
       over (partition by group_id)   
from demotable
group by group_id 

Answer 5

below is undocumented and not recomended by oracle. and can not apply in function, show error

select wm_concat(distinct name) as names from demotable group by group_id

regards zia

Answer 6

I needed this peace of code as a subquery with some data filter before aggregation based on the outer most query but I wasn't able to do this using the chosen answer code because this filter should go in the inner most select (third level query) and the filter params was in the outer most select (first level query), which gave me the error ORA-00904: "TB_OUTERMOST"."COL": invalid identifier as the ANSI SQL states that table references (correlation names) are scoped to just one level deep.

I needed a solution with no levels of subquery and this one below worked great for me:

with

demotable as
(
  select 1 group_id, 'David'   name from dual union all
  select 1 group_id, 'John'    name from dual union all
  select 1 group_id, 'Alan'    name from dual union all
  select 1 group_id, 'David'   name from dual union all
  select 2 group_id, 'Julie'   name from dual union all
  select 2 group_id, 'Charlie' name from dual
)

select distinct 
  group_id, 
  listagg(name, ',') within group (order by name) over (partition by group_id) names
from demotable
-- where any filter I want
group by group_id, name
order by group_id;

Answer 7

In 11g you can use the undocumented function wm_concat like this:

     select wm_concat(distinct name) as names from demotable group by group_id