title: Getting repeated values when using LISTAGG even for single field
body: <pre><code>CREATE TABLE source_det (
      e_id       NUMBER(10),
      sys_name   VARCHAR2(20),
      ref_id     NUMBER(10),
      sys_other  VARCHAR2(30)
  );
  
  INSERT INTO source_det VALUES(11,'SOURCE',992,null);
  INSERT INTO source_det VALUES(11,'SOURCE',637,null);
  INSERT INTO source_det VALUES(11,'SOURCE',null,'Manual');
  INSERT INTO source_det VALUES(11,'TARGET',637,null);
  
  commit;
  CREATE TABLE type_fields (
    e_id      NUMBER(10),
    field_name   VARCHAR2(20),
    integer_value NUMBER(10)
  );
  
  INSERT INTO type_fields VALUES(11,'US',123); 
  
  CREATE TABLE lookup_tab (
    l_id   NUMBER(10),
    l_value VARCHAR2(20)
  );
  
  INSERT INTO lookup_tab VALUES(123,'Unit');
  
  --My query
  
  SELECT sd.e_id,
         LISTAGG(CASE
                   WHEN sd.sys_name = 'SOURCE' THEN
                    sd.ref_id
                 END,
                 ';') WITHIN GROUP(ORDER BY ref_id DESC) AS source,
         LISTAGG(CASE
                   WHEN sd.sys_name = 'TARGET' THEN
                    sd.ref_id
                 END,
                 ';') WITHIN GROUP(ORDER BY sd.ref_id DESC) AS target,
         LISTAGG(CASE
                   WHEN sd.sys_name = 'SOURCE' AND sd.ref_id IS NULL THEN
                    DECODE(sd.sys_other,'()','',sd.sys_other)
                 END,
                 ';') WITHIN GROUP(ORDER BY 0) AS source_other,
         LISTAGG(CASE WHEN tf.field_name = 'US' THEN l_value END,';')BU
    FROM source_det sd
    LEFT JOIN type_fields tf ON(sd.e_id = tf.e_id)
    LEFT JOIN(SELECT l_id, l_value FROM lookup_tab)lt ON(lt.l_id = tf.integer_value)
   GROUP BY sd.e_id;
  
  </code></pre>
  <p>From the above set of data I need only a single row to be returned from the SELECT query but I am getting null values instead along with the required values. If sys_name is SOURCE then the result should be ref_id. If there is more than one record then it should be spilitted by delimiter ; If sys_name is TARGET then result should be ref_id If sys_name is SOURCE AND ref_id is null then it should give sys_other as a result. But from my query I am not getting a single row instead getting 4 rows. But ideally, I need only one row with e_id 11 And most important is in place of null values it could be possible that there would be () present in place of null. So, need to exclude this as well. All these things are handled in my query but I need to join one transaction table i.e type_fields with the reference table lookup_tab to get the respective value from the lookup table. But in my query</p>
  <pre><code>LISTAGG(CASE WHEN tf.field_name = 'US' THEN l_value END,';')BU
  </code></pre>
  <p>This is giving result set as -- &quot;Unit;Unit;Unit&quot; three times but ideally it should give me only once. But also handle if there are any other value except Unit. Then it should separate the data as &quot;Unit;new value(just an example)&quot; .Rest all of the things are working as expected expect the column BU.</p>
  <p>Expected Output:</p>
  <pre><code>E_ID SOURCE  TARGET  SOURCE_OTHER SOURCE
  ------ ------- ------ ------------ -------
     11 992;637  637    Manual        Unit
  </code></pre>
  <p>Current output:</p>
  <pre><code>
  E_ID SOURCE  TARGET  SOURCE_OTHER      SOURCE
  ------ ------- ------ ------------     -------
     11 992;637  637    Manual        Unit;Unit;Unit;Unit
  </code></pre>
  <p>Can someone let me know where it went wrong?</p>
  <p>Tool used: SQL Devloper(18c)</p>