Listagg in Oracle to Return Distinct Values

Retrieve distinct values with LISTAGG in Oracle 12C

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>

Listagg distinct values

This is what you have:

SQL> select d.dname,
  2         listagg(e.job, ', ') within group (order by e.job) jobs
  3  from dept d join emp e on e.deptno = d.deptno
  4  group by d.dname;

DNAME          JOBS
-------------- ------------------------------------------------------------
ACCOUNTING     CLERK, MANAGER, PRESIDENT
RESEARCH       ANALYST, ANALYST, CLERK, CLERK, MANAGER
SALES          CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN

This is what you want:

SQL> select x.dname,
  2         listagg(x.job, ', ') within group (order by x.job) jobs
  3  from (select distinct d.dname,
  4                        e.job
  5        from dept d join emp e on e.deptno = d.deptno
  6       ) x
  7  group by x.dname;

DNAME          JOBS
-------------- ------------------------------------------------------------
ACCOUNTING     CLERK, MANAGER, PRESIDENT
RESEARCH       ANALYST, CLERK, MANAGER
SALES          CLERK, MANAGER, SALESMAN

SQL>

So, yes - first find distinct values, then listagg them.

How to remove duplicate values from a aggregate list when using LISTAGG() in oracle SQL?

For a simple query, you can use a subquery:

SELECT GRADE, listagg(name, ';') within group (order by name) as names
FROM (SELECT DISTINCT GRADE, st_name || ';' || st_last_name as name
      FROM STUDENTS
     ) s
GROUP BY GRADE;

I find it odd that you are using a semi-colon both to separate st_name and st_last_name and to separate the different values. I would expect different separators to be used -- 'john;smith;jane;doe;' is harder to read than 'john,smith;jane,doe'.

How I remove duplicates from listagg

First select DISTINCT values you need, then apply LISTAGG to them. Here's an example based on Scott's schema.

SQL> -- Duplicate jobs within the department
SQL> select deptno, listagg(job, ', ') within group (order by job) jobs
  2  from emp
  3  group by deptno;

    DEPTNO JOBS
---------- ------------------------------------------------------------
        10 CLERK, MANAGER, PRESIDENT
        20 ANALYST, ANALYST, CLERK, CLERK, MANAGER
        30 CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN

SQL>
SQL> -- This won't work - DISTINCT can't be used in LISTAGG
SQL> select deptno, listagg(distinct job, ', ') within group (order by job) jobs
  2  from emp
  3  group by deptno;
select deptno, listagg(distinct job, ', ') within group (order by job) jobs
               *
ERROR at line 1:
ORA-30482: DISTINCT option not allowed for this function


SQL>
SQL> -- So - select distinct jobs first, then apply LISTAGG to it
SQL> select x.deptno, listagg(x.job, ', ') within group (order by x.job) jobs
  2  from (select distinct deptno, job
  3        from emp) x
  4  group by x.deptno;

    DEPTNO JOBS
---------- ------------------------------------------------------------
        10 CLERK, MANAGER, PRESIDENT
        20 ANALYST, CLERK, MANAGER
        30 CLERK, MANAGER, SALESMAN

SQL>

Oracle LISTAGG case return distinct values

Assuming Table2 has PERSON_ID as foreign key pointing to Table1, what you need to do is to replace

FROM Table1
LEFT JOIN Table2
ON Table2.PERSON_ID = Table1.PERSON_ID

with

FROM Table1
LEFT JOIN 
(SELECT DISTINCT PERSON_ID, DESCRIPTION FROM Table2) T2
ON T2.PERSON_ID = Table1.PERSON_ID

and then change Table2 to T2 in LISTAGG() (including in the WITHIN GROUP (ORDER BY ...) clause!)

---

Related Topics