How can I eliminate duplicate data in multiple columns query

Question

I just asked the question about how I eliminate duplicate data in a column

How can I eliminate duplicate data in column

this code below can delete duplicates in a column

with data as
(
  select 'apple, apple, apple, apple' col from dual
)
select listagg(col, ',') within group(order by 1) col
  from (
        select distinct regexp_substr(col, '[^,]+', 1, level) col
          from data
        connect by level <= regexp_count(col, ',')
       )

next question is now I do not know how to eliminate data in multiple columns

select 'apple, apple, apple' as col1, 
       'prince,prince,princess' as col2, 
       'dog, cat, cat' as col3
  from dual;

I would like to show

COL1     COL2                COL3
-----    ----------------    --------
apple    prince, princess    dog, cat

Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation. — Kaushik Nayak
– Kaushik Nayak, Commented Nov 19, 2018 at 16:11

Barbaros Özhan · Accepted Answer · 2018-11-19 16:25:04Z

You may use such a combination :

select  
    (
    select listagg(str,',') within group (order by 0)
      from
     (
      select distinct trim(regexp_substr('apple, apple, apple','[^,]+', 1, level)) as str
        from dual
     connect by level <= regexp_count('apple, apple, apple',',') + 1
     )
    ) as str1,
    (
    select listagg(str,',') within group (order by 0)
      from
     (
      select distinct trim(regexp_substr('prince,prince,princess','[^,]+', 1, level)) as str
        from dual
     connect by level <= regexp_count('prince,prince,princess',',') + 1
     )
    ) as str2,   
    (
    select listagg(str,',') within group (order by 0)
      from
     (
      select distinct trim(regexp_substr('dog, cat, cat','[^,]+', 1, level)) as str
        from dual
     connect by level <= regexp_count('dog, cat, cat',',') + 1
     )
    ) as str3    
 from dual;

 STR1         STR2           STR3
 ------  ---------------   --------
 apple   prince,princess   cat,dog

Rextester Demo

Collectives™ on Stack Overflow

How can I eliminate duplicate data in multiple columns query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related