CODEWITHSUNDEEP
sqloracle-databaseduplicatesstring-concatenationlistagg
Ori Marko
Ori Marko

Reputation: 58772

concatenate column values from multiple rows in Oracle without duplicates

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

ID
10
10
20
30
30
40

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

Upvotes: 0

Views: 1388

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 2

Radagast81
Radagast81

Reputation: 3006

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

Upvotes: 1

Related Questions