By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @tbl table (ID int identity(1,1), Value int);
insert into @tbl (Value)
values (1), (1), (1), (0), (0), (1), (0), (0), (1), (1), (1), (1);
with grp as (
select *,
case when Lag(Value) over(order by ID) = Value then 0 else 1 end gp
from @tbl
), seq as (
select *, Sum(gp) over(order by ID) s
from grp
)
select
s + 1 as GroupId,
Value,
Min(ID) MinId,
Max(ID) MaxId
from seq
group by s + 1, Value
order by GroupId;
| GroupId | Value | MinId | MaxId |
|---|---|---|---|
| 2 | 1 | 1 | 3 |
| 3 | 0 | 4 | 5 |
| 4 | 1 | 6 | 6 |
| 5 | 0 | 7 | 8 |
| 6 | 1 | 9 | 12 |