Remove duplicate values from comma separated string in Oracle

Question

I need your help with the regexp_replace function. I have a table which has a column for concatenated string values which contain duplicates. How do I eliminate them?

Example:

Ian,Beatty,Larry,Neesha,Beatty,Neesha,Ian,Neesha

I need the output to be

Ian,Beatty,Larry,Neesha

The duplicates are random and not in any particular order.

Update--

Here's how my table looks

ID   Name1   Name2    Name3     
1     a       b         c
1     c       d         a
2     d       e         a
2     c       d          b

I need one row per ID having distinct name1,name2,name3 in one row as a comma separated string.

ID    Name
1     a,c,b,d,c
2     d,c,e,a,b

I have tried using listagg with distinct but I'm not able to remove the duplicates.

Answer 1

The easiest option I would go with -

SELECT ID, LISTAGG(NAME_LIST, ',')
  FROM (SELECT ID, NAME1 NAME_LIST FROM DATA UNION
        SELECT ID, NAME2 FROM DATA UNION
        SELECT ID, NAME3 FROM DATA
      )
GROUP BY ID;

Demo.