Creating Group For Multiple Columns With Multiple Duplicates

The below table contains details of members and their policies. We need to form a group of 2 or more members if they have at least one policy in common. Member_ID Policy 101

Solution 1:

What you describe is a graph-walking algorithm. This is complicated because you may need to traverse multiple levels. For instance:

101    X1
102    Y1
102    Y2
103    Y2
103    Z1
104    Z1

These are all in the same group by 101 and 104 have nothing in common.

Recursive CTEs can handle this. For this problem, it looks like:

with edges as (select distinct t.memberid as m1, t2.memberid as m2
      from t join
           t t2
           on t2.policy = t.policy -- and t2.memberid <> t.memberid
     ),
     cte as (select m1, m2, convert(varchar(max), concat(',', m1, ',', m2, ',')) as members, 1 as lev
      from edges
      union all
      select cte.m1, e.m2, concat(members, e.m2, ','), lev + 1
      from cte join
           edges e
           on cte.m2 = e.m1
      where members not like concat('%,', e.m2, ',%') 
     )
select m1, dense_rank() over (order by min(m2)) as grouping
from cte
groupby m1;

Here is a db<>fiddle.