In this release Oracle finally added possibility to use DISTINCT inside LISTAGG function. It helps to quickly remove duplicates.

CREATE TABLE test_tbl
(
  id   NUMBER,
  name VARCHAR2(30)
);

INSERT INTO test_tbl VALUES(1, 'TOMASZ');
INSERT INTO test_tbl VALUES(1, 'TOMASZ');
INSERT INTO test_tbl VALUES(1, 'GREG');
INSERT INTO test_tbl VALUES(1, 'GREG');
INSERT INTO test_tbl VALUES(1, 'CHRIS');
INSERT INTO test_tbl VALUES(1, 'ADAM');
INSERT INTO test_tbl VALUES(2, 'ADAM');
INSERT INTO test_tbl VALUES(2, 'ADAM');

COMMIT;

Simple select with duplicates for names ADAM, GREG, TOMASZ

SELECT 
    listagg(name, ',') WITHIN GROUP(ORDER BY name) dup
  FROM test_tbl;

DUP                                      
----------------------------------------------
ADAM,ADAM,ADAM,CHRIS,GREG,GREG,TOMASZ,TOMASZ

and new way how to remove duplicates

SELECT 
    listagg(DISTINCT name, ',') WITHIN GROUP(ORDER BY name) no_dup
  FROM test_tbl;

NO_DUP             
------------------------
ADAM,CHRIS,GREG,TOMASZ

Another example duplicates and remove duplicates

SELECT 
    id, 
    listagg(name, ',') WITHIN GROUP(ORDER BY name) dup
  FROM test_tbl
GROUP BY id;

ID  DUP
--- ----------------------------------
1   ADAM,CHRIS,GREG,GREG,TOMASZ,TOMASZ
2   ADAM,ADAM

SELECT 
    id, 
    listagg(DISTINCT name, ',') WITHIN GROUP(ORDER BY name) no_dup
  FROM test_tbl
GROUP BY id;

ID	NO_DUP
--- ----------------------------------
1	ADAM,CHRIS,GREG,TOMASZ
2	ADAM

Have a fun 🙂

  Tomasz