oracle-databasegroup-byoracle11gaggregate-functions

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


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?


Solution

  • 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