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
For me second example does not work properly… It removes duplicate results even they does not duplicate for key column
Pingback: one more stragg | Laurent Schneider
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;
it’s ok, but you do two full table scans instead of one
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?
Pingback: Oracle 19c – Laurent Schneider
This is ingenious, Laurent. I’ve been looking for this solution on and off for quite some time. Thank you very much.