How to use Oracle's LISTAGG function with a unique filter? [duplicate]
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?
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
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
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