Как сгруппировать пользователей по одному или двум параметрам

with s as
(
 select
  UserID, Name, Email, UserID as grp, cast(formatmessage('/%d/', UserID) as varchar(max)) as p
 from
  #a
 
 union all
 
 select
  a.UserID, a.Name, a.Email, s.UserID as grp, b.p + s.p
 from
  s join
  #a a on a.UserID > s.UserID and (a.Name = s.Name or a.Email = s.Email) cross apply
  (select cast(formatmessage('/%d', a.UserID) as varchar(max))) b(p)
 where
  s.p not like '%' + b.p + '/%'
)
select
 x.*
from
 (
  select top (1) with ties grp, UserID, Name, Email from s order by row_number() over (partition by UserID order by grp)
 ) x
order by
 grp, UserID;

with s as
(
 select
  UserID, Name, Email, UserID as grp
 from
  #a
 
 union all
 
 select
  a.UserID, a.Name, a.Email, s.UserID as grp
 from
  s join
  #a a on a.UserID > s.UserID and (a.Name = s.Name or a.Email = s.Email)
),
x as
(
 select
  UserID, Name, Email, grp, count(*) over (partition by grp) as c
 from
  s
)
select
 UserID, Name, Email, grp
from
 (select top (1) with ties * from x order by row_number() over (partition by UserID order by c desc)) t
order by
 grp, UserID;

 create table #a (UserID int, Name varchar(25), Email Varchar(50))
 
insert into #a
values
(1, 'Marvin Cottrell', '123456@gmail.com'),
(2, 'Colleen Morrison', 'somebody@gmail.com'),
(3, 'Colleen Morrison', 'cm@gmail.com'),
(4, 'Dante Henry', '765@gmail.com'),
(5, 'Willie Alcock', '123456@gmail.com'),
(6, 'Marvin Cottrell', 'lib@gmail.com'),
(7, 'Cecilia Mueller', '765@gmail.com'),
(8, 'Cathal Compton', '765@gmail.com'),
(9, 'Giovanni Tapia', '123456@gmail.com'),
(10, 'Humphrey Macias', 'somebody@gmail.com'),
(11, 'Humphrey Macias', '11111@gmail.com')