最好只获取列表中的DISTINCT项，然后可以使用'|'作为LISTAGG中的分隔符，而不是','：

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

如果你不能使用DISTINCT，那么你需要匹配整个术语，你可以通过在每个术语前面加上一个分隔符，然后聚合，然后匹配重复的术语，包括周围的分隔符，并删除重复项;最后，你需要删除前导和尾随分隔符以及术语之间的双分隔符。
注意：如果你简单地匹配部分项，你会发现abc|abcdef|defghi被转换为abcdefghi，这是不正确的。
像这样：

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;

其中，对于样本数据：

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;

输出：
| ID|用户名|
| - -----|- -----|
| 1| abc| abcdef| def| defghi|
fiddle

SELECT
  ID,
  REGEXP_REPLACE(
    LISTAGG(user_name, ',') WITHIN GROUP (ORDER BY user_name),
    '([^,]+)(,|$)',
    '\1\2'
  ) AS USER_NAME
FROM my_table
GROUP BY ID;

只需在调用LISTAGG时将,替换为|即可：

SELECT
ID,
LISTAGG (user_name, '|') WITHIN GROUP (ORDER BY user_name )
From my_table
group by id