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.

score 2 · Answer 1 · answered Jun 13 '23 at 10:29

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;

answered Jun 13 '23 at 10:29

Littlefoot

131,892
15
35
57

Already tried that. It is very good but the LISTAGG should be part of a long query and can not be a sepaerate SELECT. Sorry forgot to add this to the question. Edit it now. – BiSaM Jun 13 '23 at 10:33
In that case, do similarly to what I did - create a CTE (or a subquery) which will return aggregates usernames, and then join it (the CTE or subquery) to the rest of your tables (probably on the ID column). – Littlefoot Jun 13 '23 at 10:38
ok try it but is there no "easier" way then a subquery? – BiSaM Jun 13 '23 at 10:52
It would be, if you're on (at least) Oracle 19c whose LISTAGG supports DISTINCT, so you'd simply `LISTAGG(DISTINCT user_name, '|') ...` – Littlefoot Jun 13 '23 at 11:04
yes I know that for 19c it is easy ^^. For other columns I found a way to do it but this one I'm struggling.. – BiSaM Jun 13 '23 at 11:17

score 0 · Answer 2 · answered Jun 13 '23 at 11:01

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

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

2 Answers2