LISTAGG DISTINCT Oracle Database 19C

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.