Straight forward running total and lag
😎
;WITH BASE AS
(
SELECT [R], [D]
FROM (
VALUES
(1,'A')
,(2,'B')
,(3,'B')
,(4,'A')
,(5,'B')
,(6,'B')
,(7,'A')
,(8,'A')
,(9,'A')) AS X([R],[D]))
,PRIME_GROUP AS
(
SELECT
B.R
,B.D
,CASE WHEN LAG(B.D,1,'') OVER (ORDER BY B.R) = B.D THEN 0 ELSE 1 END AS GR_ID
FROM BASE B
)
SELECT
PG.R
,PG.D
,SUM(PG.GR_ID) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY PG.R
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GroupID
FROM PRIME_GROUP PG;
Results
R D GroupID
-- -- -------
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5