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
Thanks for sharing this wonderful information I really enjoyed reading it. If you have time, please visit our posts: Internet Speed Test.
ReplyDeleteGreat site. I lean something new every time I visit. Thank you
ReplyDeletefor this thing i cannot get a new so i have to lookk for machine lathes for sale
ReplyDelete