Process distinct comma-separated value in oracle

Question

I have a column with the following data:

Brand
-------------
Audi, Opel, Ford
Skoda, Renault
Audi, BMW
Audi, Volkswagen, Opel
Toyota, Hyundai

I would like to have query which automates assign the data into group as following:

Brand
-------------------
Audi, Opel, Ford, BMW, Volkwagen
Skoda, Renault
Toyota, Hyundai

Note that if we insert another record into the table like this ...

Toyota, BMW

... the required output would be:

Brand
-------------------
Audi, Opel, Ford, BMW, Volkwagen, Toyota, Hyundai
Skoda, Renault

Answer 1

This is an interesting and difficult problem, obscured by your poor data model (which violates First Normal Form). Normalizing the data - and de-normalizing at the end - is trivial, it's just an annoyance (and it will make the query much slower). The interesting part: the input groups are the nodes of a graph, two nodes are connected if they have a "make" in common. You need to find the connected components of the graph; this is the interesting problem.

Here is a complete solution (creating the testing data on the fly, in the first factored subquery in the with clause). Question for you though: even assuming that this solution works for you and you put it in production, who is going to maintain it in the future?

EDIT It occurred to me that my original query can be simplified. Here is the revised version; you can click on the Edited link below the answer if you are curious to see the original version.

with
  sample_data (brand) as (
    select 'Audi, Opel, Ford'       from dual union all
    select 'Skoda, Renault'         from dual union all
    select 'Audi, BMW'              from dual union all
    select 'Audi, Volkswagen, Opel' from dual union all
    select 'Toyota, Hyundai'        from dual union all
    select 'Tesla'                  from dual
  )
, prep (id, brand) as (
    select rownum, brand 
    from   sample_data
  )
, fnf (id, brand) as (
    select p.id, ca.brand
    from   prep p  cross apply
           ( select  trim(regexp_substr(p.brand, '[^,]+', 1, level)) as brand
             from    dual
             connect by level <= regexp_count(p.brand, '[^,]+')
           ) ca
  )
, g (b1, b2) as (
    select distinct fnf1.brand, fnf2.brand
    from   fnf fnf1 join fnf fnf2 on fnf1.id = fnf2.id
  )
, cc (rt, brand) as (
    select  min(connect_by_root b1), b2
    from    g
    connect by nocycle b1 = prior b2
    group   by b2
  )
select listagg(brand, ', ') within group (order by null) as brand
from   cc
group  by rt;

Output:

BRAND                                        
---------------------------------------------
Audi, BMW, Ford, Opel, Volkswagen
Hyundai, Toyota
Renault, Skoda
Tesla

Answer 2

That is standard Connected components problem. You can find fast pl/sql solution for production use here: http://orasql.org/2017/09/29/connected-components/

Or in case of just educational purposes, you can use SQL-only solution: https://gist.github.com/xtender/b6e5cac4dec461c0121145b0e62c5cf5

with t(Brand) as (
    select 'Audi, Opel, Ford' brand from dual union all
    select 'Skoda, Renault'         from dual union all
    select 'Audi, BMW'              from dual union all
    select 'Audi, Volkswagen, Opel' from dual union all
    select 'Toyota, Hyundai'        from dual union all
    select 'Tesla'                  from dual union all
    select 'A'||level||', A'||(level+1)   from dual connect by level<=500 union all
    select 'B'||level||', B'||(level+1)   from dual connect by level<=500 union all
    select 'C'||level||', C'||(level+1)   from dual connect by level<=500
)
,split_tab as (
   select
      dense_rank()over(order by t.brand) rn
     ,x.*
   from t,
       xmltable(
         'ora:tokenize(concat(",",.),",")[position()>1]'
         passing t.brand
         columns 
             n for ordinality
            ,name varchar2(20) path 'normalize-space(.)' 
       ) x
)
,pairs as (
   select 
      t1.rn, t1.name name1, t2.name name2
   from split_tab t1
       ,split_tab t2
   where t1.rn=t2.rn
)
select listagg(x,',')within group(order by x) 
from (
   select x, min(root) grp
    from (
    select distinct connect_by_root(name1) root, name1 x
    from pairs
    connect by nocycle 
         prior name1 = name2
    )
   group by x
)
group by grp
/

PS. I've split my solution into smallest possible steps, so you can check each CTE separately step-by-step to view how to get results.