Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE my_table (id, user_name) AS
SELECT 1, 'abc' FROM DUAL UNION ALL
SELECT 1, 'abc' FROM DUAL UNION ALL
SELECT 1, 'abcdef' FROM DUAL UNION ALL
SELECT 1, 'def' FROM DUAL UNION ALL
SELECT 1, 'def' FROM DUAL UNION ALL
SELECT 1, 'defghi' FROM DUAL;
6 rows affected

SELECT ID,
       REPLACE(
         TRIM(
           BOTH '|' FROM
           REGEXP_REPLACE(
             LISTAGG('|' || user_name || '|', NULL) WITHIN GROUP (ORDER BY user_name),
             '(\|[^|]+\|)(\1)+', '\1'
           )
         ),
         '||',
         '|'
       ) AS user_names
FROM   my_table
GROUP BY id;


ID
USER_NAMES


1
abc|abcdef|def|defghi