@luis Cazares:

No no, thank you 🙂

@Eirikur Eiriksson: I learned a lot from your solution (never saw 'LAG' and 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' function/syntax before), thanks for that. But your query is returning invalid results for the table:

insert into #Countries (Country)

select 'C1' union all

select 'C2' union all

select 'C2' union all

select 'C2' union all

select 'C2' union all

select 'C2' union all

select 'C2' union all

select 'C2' union all

select 'C2' union all

select 'C3' union all

select 'C3'

I get:

1C11

2C23

3C23

4C23

5C24

6C24

7C24

8C25

9C25

10C35

11C35

Instead of:

1C11

2C22

3C22

4C22

5C23

6C23

7C23

8C24

9C24

10C35

11C35