concatenate column values from multiple rows in Oracle without duplicates

Question

I can concatenate column values from multiple rows in Oracle using LISTAGG

But I want to avoid duplicates

Currently it return duplicates

select LISTAGG( t.id,',') WITHIN GROUP (ORDER BY t.id) from table t;

for example for data

Returns 10,10,20,30,40,40

Instead 10,20,30,40

And I can't use distinct inside LISTAGG

select LISTAGG( distinct t.id,',') WITHIN GROUP (ORDER BY t.id) from table t;

Error

ORA-30482: DISTINCT option not allowed for this function

score 2 · Accepted Answer · answered Dec 04 '19 at 08:36

2

One option would be using regexp_replace():

select regexp_replace(
                      listagg( t.id,',') within group (order by t.id)
                      , '([^,]+)(,\1)+', '\1') as "Result"
  from t

Demo

answered Dec 04 '19 at 08:36

Barbaros Özhan

59,113
10
31
55

This does not work in all cases: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=5f41fe131e9cc094f75c40b3b652a30a. – Gordon Linoff Dec 04 '19 at 12:42

score 1 · Answer 2 · answered Dec 04 '19 at 08:22

1

You can put the distinct in a subquery:

select LISTAGG( t.id,',') WITHIN GROUP (ORDER BY t.id) from (SELECT DISTINCT t.id FROM TABLE) t

answered Dec 04 '19 at 08:22

Radagast81

2,921
1
7
21

Thanks for answering, It's part of complex query and I wonder if I can do it without another subquery? – Ori Marko Dec 04 '19 at 08:35

concatenate column values from multiple rows in Oracle without duplicates

2 Answers2