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

7 thoughts on “distinct listagg

  1. Boris Mitioglov

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

  2. Pingback: one more stragg | Laurent Schneider

  3. ora777

    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;

  4. John Rose

    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?

  5. Pingback: Oracle 19c – Laurent Schneider

  6. Forbidden Path Traveler

    This is ingenious, Laurent. I’ve been looking for this solution on and off for quite some time. Thank you very much.

Comments are closed.