How remove duplicates from sql list?

Question

<property name="xxDescribtionNumbers" formula="(
                select nvl(listagg(A.NUMBER, ', ') within group (order by A.NUMBER),' ') 
                from  ENTER E
                left outer join ADRESS A on A.ID = E.ADRESS_ID
                where E.BUILDING_ID = ID
                )" />

This will make a list of numbers but can contains duplicities, but I don't want any duplicities.

Answer 1

You can do this by removing the values in a subquery:

select coalesce(listagg(va.CISLO, ', ') within group (order by va.CISLO), ' ') 
from (select distinct a.cislo
      from VCHOD V left join
           ADRESA A
           on A.ID = V.ADRESA_ID
      where V.BUDOVA_ID = ID
     ) va;