Remove repetition in result from listagg

listagg_ordenado.sql

drop table ListAggTest;

CREATE TABLE ListAggTest AS (
   SELECT 1 as  num1,DECODE(mod(rownum,2),1,'A|B','B|A') Num2 FROM dual CONNECT BY rownum<=10
); 

select * from ListAggTest;
/*
      NUM1 NUM
---------- ---
         1 A|B
         1 B|A
         1 A|B
         1 B|A
         1 A|B
         1 B|A
         1 A|B
         1 B|A
         1 A|B
         1 B|A

10 rows selected.
*/


set line 200
col list for a60
select num1, listagg(num2,'|') within group (order by num1) as list  from ListAggTest group by num1;BDADOS bdados1 > BDADOS bdados1 >
/*
      NUM1 LIST
---------- ------------------------------------------------------------
         1 A|B|A|B|A|B|A|B|A|B|B|A|B|A|B|A|B|A|B|A
*/

-- Goal: A|B

create or replace function distinct_text (v_text in varchar2) return varchar2 is
    v_return varchar2(4000);
begin
    with agrupado as (
         select distinct trim(regexp_substr(v_text,'[^|]+', 1, level))  as coluna
               from dual
            connect by trim(regexp_substr(v_text, '[^|]+', 1, level)) is not null ORDER by 1)
         select listagg(coluna,'|') within group (order by coluna) into v_return from agrupado;
   return v_return;
end;    
                            
select num1, distinct_text(listagg(num2,'|') within group (order by num1)) as list  from ListAggTest group by num1;
/*
      NUM1 LIST
---------- ------------------------------------------------------------
         1 A|B
*/