distinct listagg

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache

With one listagg

SELECT 
  DEPTNO,
  LISTAGG (JOB, ',') 
    WITHIN GROUP (ORDER BY JOB) JOBS
FROM (
  SELECT DISTINCT DEPTNO, JOB  FROM EMP)
GROUP BY DEPTNO;

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

ok, it was not that hard, but it gets more difficult with two listagg’s

SELECT 
  LISTAGG (job, ',') 
    WITHIN GROUP (ORDER BY job) jobs,
  LISTAGG (deptno, ',') 
    WITHIN GROUP (ORDER BY deptno) deptnos
FROM (
  SELECT 
    DECODE(
      ROW_NUMBER () OVER (
        PARTITION BY deptno 
        ORDER BY 1),
      1, deptno) deptno,
    DECODE (
      ROW_NUMBER () OVER (
        PARTITION BY job 
        ORDER BY 1),
      1, job) job
  FROM emp
);
DEPTNOS  JOBS                                      
-------- ----------------------------------------
10,20,30 ANALYST,CLERK,MANAGER,PRESIDENT,SALESMAN

Too bad the DISTINCT keyword was not implemented

Author: Laurent Schneider

Oracle Certified Master

5 thoughts on “distinct listagg”

  1. For me second example does not work properly… It removes duplicate results even they does not duplicate for key column

  2. how about this

    WITH A AS (SELECT DISTINCT object_type FROM dba_objects),
    b AS (SELECT DISTINCT status FROM dba_objects)
    SELECT (SELECT listagg(object_type,’,’) WITHIN GROUP (ORDER BY object_type) ot FROM A) x,
    (select listagg(status,’,’) within group (order by status) s from b) y
    from dual;

  3. And how would it change if you have eight other columns that must be returned? Only the listagg column is a concern for duplicates.

    Then how would you also convince listagg to pick up data from records in which the listagg column is null?

    And how would the two issues together look?

Leave a Reply