This has had me scratching my head for a few days.
First I thought I could do this with a variation on 'Gaps and Islands', then I though it was a variation on 'Bin Packing'. In the end I've resorted to a row-by-row approach. I'd be very interested to see a set-based solution to this.
To enable me to deal with the data one row at a time, I had to introduce something that made each row unique - hence the cte with a UniqueID - once you've got that it's a simple loop until all rows have had a grouping assigned.
if object_id('a','U') is not null drop table a create table a(Id int, Name VARCHAR(10), [Group] int not null default 0) insert into a (Id, Name) VALUES (10, 'a'),(10,'b'), (20,'a'), (20,'b'),(30,'d'),(40,'e'), (10,'a'), (50,'a'), (20,'f') while exists (select top 1 Id from a where [group] = 0) begin with r as (select *, row_number()over(order by (select null)) as UniqueId from a) update a set [group] = case when (select min([group]) from r a2 where [group] > 0 and not (a2.id = a.id or a2.Name = a.name) and [group] not in (select [group] from r a2 where [group] > 0 and (a2.id = a.id or a2.Name = a.name)))is not null then (select min([group]) from r a2 where [group] > 0 and not (a2.id = a.id or a2.Name = a.name) and [group] not in (select [group] from r a2 where [group] > 0 and (a2.id = a.id or a2.Name = a.name))) else (select max([group]) from r)+1 end from r a where UniqueId in (select top 1 UniqueId from r where [group] = 0 order by UniqueId) end select * from a
returns
Id Name Group ----------- ---------- ----------- 10 a 1 10 b 2 20 a 2 20 b 1 30 d 1 40 e 1 10 a 3 50 a 4 20 f 3 (9 row(s) affected)
14 People are following this question.