LISTAGG DISTINCT - Remove Duplicates - Oracle 19c

LISTAGG function was introduced in Oracle 11g. LISTAGG function aggregates the result set in multiple rows into one single column. 

In Oracle 12c R2 LISTAGG function was enhanced to manage situations where the length of the concatenated string is too long by providing us ON OVERFLOW TRUNCATE clause. 

With Oracle 19c Database LISTAGG function can also remove the duplicate values by using DISTINCT keyword. This feature was requested by many developers from the inception of LISTAGG function, and now we do not need to write complex SQL to remove the duplicates from the list.

Following SQL is the example of LISTAGG with DISTINCT keyword in Oracle 19c.
SQL> select d.deptno, dname,
  2  listagg(distinct job, ',') within group (order by job) jobs_in_dept
  3  from dept d, emp e
  4  where e.deptno = d.deptno
  5  group by d.deptno, dname;

    DEPTNO DNAME          JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
        10 ACCOUNTING     CLERK,MANAGER,PRESIDENT
        20 RESEARCH       ANALYST,CLERK,MANAGER
        30 SALES          CLERK,MANAGER,SALESMAN
If we execute the above SQL without DISTINCT keyword, we will get following result.
SQL> select d.deptno, dname,
  2  listagg(job, ',') within group (order by job) jobs_in_dept
  3  from dept d, emp e
  4  where e.deptno = d.deptno
  5  group by d.deptno, dname;

    DEPTNO DNAME          JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
        10 ACCOUNTING     CLERK,MANAGER,PRESIDENT
        20 RESEARCH       ANALYST,ANALYST,CLERK,CLERK,MANAGER
        30 SALES          CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
In Oracle 18c or lower versions of Oracle database (like Oracle 12c or Oracle 11g), we can remove the duplicate values by removing the duplicate first in nested SQL (or in WITH clause) and then using the LISTAGG function.
SQL> select deptno, dname,
  2  listagg(job, ',') within group (order by job) jobs_in_dept
  3  from
  4  (
  5    select distinct
  6    d.deptno, d.dname, e.job
  7    from dept d, emp e
  8    where e.deptno = d.deptno
  9  )
 10  group by deptno, dname;

    DEPTNO DNAME          JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
        10 ACCOUNTING     CLERK,MANAGER,PRESIDENT
        20 RESEARCH       ANALYST,CLERK,MANAGER
        30 SALES          CLERK,MANAGER,SALESMAN
We can also use REGEXP_REPLACE to remove the duplicates from the list generated by LISTAGG
SQL> with data as
  2  (
  3  select d.deptno, dname,
  4  listagg(job, ',') within group (order by job) jobs_in_dept
  5  from dept d, emp e
  6  where e.deptno = d.deptno
  7  group by d.deptno, dname
  8  )
  9  select deptno, dname,
 10  regexp_replace(jobs_in_dept,'([^,]+)(,\1)+', '\1') jobs_in_dept
 11  from data;

    DEPTNO DNAME          JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
        10 ACCOUNTING     CLERK,MANAGER,PRESIDENT
        20 RESEARCH       ANALYST,CLERK,MANAGER
        30 SALES          CLERK,MANAGER,SALESMAN

Related Posts -
 

3 comments:

  1. Thanks for sharing this wonderful information I really enjoyed reading it. If you have time, please visit our posts: Internet Speed Test.

    ReplyDelete
  2. Great site. I lean something new every time I visit. Thank you

    ReplyDelete
  3. for this thing i cannot get a new so i have to lookk for machine lathes for sale

    ReplyDelete