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