How to identify a key change in an SQL query?

Question

How to write a query that assigns a group number to adjacent rows that have the same key value? I cannot use a simple GROUP BY because this key can be used in multiple sets in different places of the result set.

For example, suppose I want to compress these lines so that adjacent registration records for adjacent time periods can be compressed into a single registration record.

Here are some test data ...

declare @enrollments table ( [Id] int not null identity(1,1), [PlanCode] nvarchar(10) not null, -- For simplicity, this is the key. [BeginDate] int not null, [EndDate] int null ); insert into @enrollments (PlanCode, BeginDate, EndDate) values ('81-3', 20160101, null) ,('80-3', 20150701, 20160101) ,('80-3', 20150301, 20150701) ,('80-3', 20150101, 20150301) ,('80-1', 20140517, 20150101) ,('80-3', 20120906, 20140517) ,('80-1', 20120101, 20120906); -- Source data. select * from @enrollments order by BeginDate desc; -- Goal results. select [Id] = 1, [PlanCode] = '81-3', [BeginDate] = 20160101, [EndDate] = NULL, [GroupNum] = 1 union select [Id] = 2, [PlanCode] = '80-3', [BeginDate] = 20150701, [EndDate] = 20160101, [GroupNum] = 2 union select [Id] = 3, [PlanCode] = '80-3', [BeginDate] = 20150301, [EndDate] = 20150701, [GroupNum] = 2 union select [Id] = 4, [PlanCode] = '80-3', [BeginDate] = 20150101, [EndDate] = 20150301, [GroupNum] = 2 union select [Id] = 5, [PlanCode] = '80-1', [BeginDate] = 20140517, [EndDate] = 20150101, [GroupNum] = 3 union select [Id] = 6, [PlanCode] = '80-3', [BeginDate] = 20120906, [EndDate] = 20140517, [GroupNum] = 4 union select [Id] = 7, [PlanCode] = '80-1', [BeginDate] = 20120101, [EndDate] = 20120906, [GroupNum] = 5; 

So give this data ...

In its simplest form, I basically need to figure out how to define GroupNum. As soon as I have, I could GROUP BY and get the other clusters that I need.

The date component means that the "normal" grouping is not working. I really hope to find a way to figure this out without using a cursor loop. I have been banging my head on this for several days. It's time to call a friend!

Thanks! Ian