Retrieve distinct values with LISTAGG in Oracle 12C

Question

I am trying to retrieve multiple concatenated distinct varchars (named CODE in query) from multiple rows on multiple columns using LISTAGG in oracle 12C, LISTAGG(distinct...) solves the problem on 19c but I must work with 12c.

Unexpected result

I get the above result using this query:


   SELECT
          T.c1 A,
          T.c2 B,
          LISTAGG( TI.CODE , ';' ) WITHIN GROUP (ORDER BY  TI.CODE) AS  COLUMNX1,
          LISTAGG( TE.CODE, ' ;') WITHIN GROUP (ORDER BY  TE.CODE ) AS COLUMNX2,    
          LISTAGG(TR.CODE, '; ') WITHIN GROUP (ORDER BY TR.CODE ) AS COLUMNX3
   FROM  TABLE1 T
          INNER join  TABLE_I TI on  TI.fk_c2 = T.c2
          INNER join  TABLE_E TE on TE.fk_c2 = T.c2
          INNER join TABLE_R TR on TR.fk_c2 = T.c2
          WHERE T.d = *parameter*
          GROUP BY
          T.c1,
          T.c2;

I want to retrieve this :

Expected result

The yellow marked strings should not be retrieved.

In evey line of the query result, the columns COLUMNX1, COLUMNX2, COLUMNX3 have the same number of concatenated strings, that's why I have the duplication problem.

furthermore, TABLE_I, TABLE_E and TABLE_R all have a foreign key fk_c2 that references TABLE1.c2

EDIT:

I added a with Clause to retrieve distinct values first then I joined it to my select statement

Expected result is retrieved with this query


WITH TEMP AS (

     SELECT fk_c2, LISTAGG(code, ',')  WITHIN GROUP (ORDER BY code) AS X1 
     FROM (
          SELECT DISTINCT * 
          FROM TABLE_I
          GROUP BY fk_c2 ) COLUMNX1

     INNER JOIN
    (SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X2
      FROM (
           SELECT DISTINCT * 
           FROM TABLE_E)
           GROUP BY fk_c2 ) COLUMNX2
     ON COLUMNX1.fk_c2 = COLUMNX2.fk_c2

     INNER JOIN
    (SELECT fk_c2, LISTAGG(code, ',') WITHIN GROUP (ORDER BY code) AS X3
     FROM(
          SELECT DISTINCT * 
          FROM TABLE_R)
          GROUP BY fk_c2 ) COLUMNX3    
     ON COLUMNX1.fk_c2 = COLUMNX3.fk_c2
       )

     SELECT
         T.c1 A,
         T.c2 B,
         tmp.X1,
         tmp.X2,
         tmp.X3
  FROM  TABLE1 T
         INNER join  temp tmp on  tmp.fk_c2 = T.c2
         WHERE T.d = *parameter*
         GROUP BY
         T.c1,
         T.c2
         tmp.X1,
         tmp.X2,
         tmp.X3;

user_9097906 · Answer

You'll need additional step: first find distinct values, then aggregate them. For example:

SQL> with test (id, col) as
  2    (select 1, 'x' from dual union all
  3     select 1, 'x' from dual union all
  4     --
  5     select 2, 'w' from dual union all
  6     select 2, 't' from dual union all
  7     select 2, 'w' from dual union all
  8     --
  9     select 3, 'i' from dual
 10    ),
 11  -- first find distinct values ...
 12  temp as
 13    (select distinct id, col from test)
 14  -- ... then aggregate them
 15  select id,
 16         listagg(col, ';') within group (order by col) result
 17  from temp
 18  group by id;

        ID RESULT
---------- ----------
         1 x
         2 t;w
         3 i

SQL>

Retrieve distinct values with LISTAGG in Oracle 12C

1 Answers