title: How to select distinct values while using multiple LISTAGG() function in select clause?
body: <p>I am trying to retrieve race_code,chara_code and reason_code as a list using following query:</p>
  
  <pre><code>SELECT a.pid,
         LISTAGG(a.rc, ',') WITHIN GROUP (ORDER BY a.rc) AS race,
         LISTAGG(a.cc, ',') WITHIN GROUP (ORDER BY a.cc) as chara_codes,
         LISTAGG(a.rrc, ',') WITHIN GROUP (ORDER BY a.rrc) AS removal_reason
  FROM (
     SELECT UNIQUE
            p.person_id pid,
            r.race_code rc,
            c.characteristic_code cc,
            rr.removal_reason_code rrc
       FROM person p left outer join race r on p.person_id = r.person_id
            left outer join characteristic c on p.person_id = c.person_id
            left outer join placement_episode pe on p.person_id = pe.child_id
            left outer join removal_reason rr on pe.placement_episode_id = rr.placement_episode_id
       ) a
  GROUP BY a.pid
  </code></pre>
  
  <p>I tried this query after referencing some links like <a href="https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values">link1</a> and <a href="https://oracle-base.com/articles/misc/string-aggregation-techniques" rel="nofollow noreferrer">link2</a>. But after doing this also I am not able to get unique values for all fields. </p>
  
  <p>My o/p is coming like:</p>
  
  <pre><code>pid      race_code     chara_code      reason_code
   1        a,b,b,c     c1,c1,c2,c3     r1,r2,r3,r3
   2       a,c,d,d,d      c1,c2,c2        r3,r3
  
  and so on.
  </code></pre>
  
  <p>If I try to retrieve only one one field at a time with keeping required join opeartions then it's giving correct result. But for multiple LISTAGG() functions its repeating values.<br>
  I am not getting  a way to do this. Is there any other way in which I can get distinct values?</p>