Transitive grouping with recursive sql, Sql Server

Transitive grouping with recursive sql

create table T (ID int, FirstName varchar(4), LastName varchar(5), DateOfBirth datetime, BirthCountry varchar(5));
insert into T (ID, FirstName, LastName, DateOfBirth, BirthCountry)
values
    (1, 'John', 'Doe', '1983-01-01 00:00:00', 'Grand'),
    (2, 'Jane', 'Doe', '1982-02-08 00:00:00', 'Grand'),
    (3, 'Adam', 'Wayne', '1983-01-01 00:00:00', 'Grand'),
    (4, 'Kay', 'Wayne', '1976-06-08 00:00:00', 'Grand'),
    (5, 'Matt', 'Knox', '1983-01-01 00:00:00', 'Hay');

with data as (
    select LastName, DateOfBirth, BirthCountry, row_number() over (order by LastName, DateOfBirth, BirthCountry) as grpNum
    from T group by LastName, DateOfBirth, BirthCountry
), r as (
    select d.LastName, d.DateOfBirth, d.BirthCountry, d.grpNum, cast('|'   + cast(d.grpNum as varchar(8)) + '|' as varchar(1024)) as equ
    from data as d
    union all
    select d.LastName, d.DateOfBirth, d.BirthCountry, r.grpNum, cast(r.equ + cast(d.grpNum as varchar(8)) + '|' as varchar(1024))
    from r inner join data as d
            on      d.grpNum > r.grpNum and charindex('|' + cast(d.grpNum as varchar(8)) + '|', r.equ) = 0
               and (d.LastName = r.LastName or d.DateOfBirth = r.DateOfBirth)
               and  d.BirthCountry = r.BirthCountry
), g as (
    select LastName, DateOfBirth, BirthCountry, min(grpNum) as grpNum
    from r group by LastName, DateOfBirth, BirthCountry
)
select t.*, dense_rank() over (order by g.grpNum) + 100 as GroupID
from T as t 
    inner join g
        on  g.LastName = t.LastName and g.DateOfBirth = t.DateOfBirth and g.BirthCountry = t.BirthCountry


run \| edit \| history \| help	0