How to change comma to pipe and remove duplicates and trim last delimiter in LISTAGG

Question

I have a query which result is: (EDIT: 13.06.2023 12:30)

|ID|USER_NAME           |
|--|--------------------|
|1 |A1,A1,A1,B12,B12,C32|
|2 |A1                  |
|3 |B12,C32             |

Query for the above result:

SELECT 
ID,
LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name )
From my_table
GROUP BY ID;

What I have tried to replace ',' with '|' and remove the duplicates and trim the last delimiter is:

SELECT
ID,
RTRIM(REGEXP_REPLACE(LISTAGG (user_name, ',') WITHIN GROUP (ORDER BY user_name ), '([^,]+)(,|\1)+', '\1|'),'|') 
From my_table 
GROUP BY ID;

The output is:

|ID|USER_NAME   |
|--|------------|
|1 |A1|2|B12|C32|
|2 |A1          |
|3 |B12|C32     |

EDIT: The LISTAGG and REGEXP should be part of a longer query and not be a standalone SELECT (output of LISTAGG should be a column of a long query). The example should only show what output I want to have in the end.

The second value for user_name and ID 1 is not correct.
How can I get the correct values? What is wrong in the query?
Thank you.

Answer 1

You may use collect aggregation to get distinct values and then use listagg over collection.

create table sample_table (id int, user_name varchar2(10))

begin
  insert into sample_table values(1, 'A1');
  insert into sample_table values(1, 'A1');
  insert into sample_table values(1, 'A1');
  insert into sample_table values(1, 'B12');
  insert into sample_table values(1, 'B12');
  insert into sample_table values(1, 'C32');
  insert into sample_table values(2, 'A1');
  insert into sample_table values(3, 'B12');
  insert into sample_table values(3, 'C32');
  insert into sample_table values(3, 'B12');
  commit;
end;/

with grouped as (
  select
    id,
    cast(collect(distinct user_name) as sys.odcivarchar2list) as qwe
  from sample_table
  group by id
)
select
  id,
  (
    select
      listagg(column_value, '|') within group(order by column_value) as user_name
    from table(grouped.qwe)
  ) as user_name
from grouped

ID	USER_NAME
1	A1|B12|C32
2	A1
3	B12|C32

fiddle

Answer 2

First select distinct values, and then apply listagg to already distinct data set:

WITH
   temp
   AS
      (SELECT DISTINCT id, user_name
         FROM my_table)
  SELECT id, 
         LISTAGG (user_name, '|') WITHIN GROUP (ORDER BY user_name)
    FROM temp
GROUP BY id;