How to use Oracle's LISTAGG function with a unique filter?
OracleGroup ByOracle11gAggregate FunctionsOracle Problem Overview
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?
Oracle Solutions
Solution 1 - Oracle
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
Solution 2 - Oracle
Super simple answer - solved!
my full answer [here][1] 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
[1]: https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values/22624072#22624072 "here"
Solution 3 - Oracle
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
Solution 4 - Oracle
select group_id,
listagg(name, ',') within group (order by name) as names
over (partition by group_id)
from demotable
group by group_id
Solution 5 - Oracle
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
Solution 6 - Oracle
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;
Solution 7 - Oracle
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