Oracle 11g Release 2

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

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;/
1 rows affected

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