Hi

Firstly you need some way to order the rows by, so I've added the seq column to order on. Then it is basically a find the islands query. This should do the trick

Declare @t table(seq int, val varchar(20))

insert @t

select 11, 'test_1' union all

select 12, 'test_2' union all

select 23, 'Total' union all

select 34, 'test_1' union all

select 35, 'test_2' union all

select 36, 'test_3' union all

select 47, 'Total' union all

select 48, 'test_1' union all

select 49, 'test_2' union all

select 50, 'test_3' union all

select 61, 'test_4' union all

select 62, 'Total' union all

select 63, 'test_1' union all

select 74, 'test_2' union all

select 85, 'Total'

SELECT SEQ, VAL, CASE WHEN G > T THEN T ELSE G END

FROM (

SELECT SEQ, VAL,

ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY LEFT(val,4) ORDER BY SEQ) + 1 G,

ROW_NUMBER() OVER (PARTITION BY LEFT(val,4) ORDER BY SEQ) T

FROM @t

) a

ORDER BY SEQ

Edit:

After thinking a bit more about this, if your values between 'Total's aren't consistently prefixed the query above will not work as expected. Then following will allow for it

SELECT SEQ, VAL, CASE WHEN G > T THEN T ELSE G END, G, T

FROM (

SELECT SEQ, VAL,

ROW_NUMBER() OVER (ORDER BY SEQ) - ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) + 1 G,

ROW_NUMBER() OVER (PARTITION BY CASE WHEN VAL = 'Total' THEN 1 ELSE 0 END ORDER BY SEQ) T

FROM @t

) a

ORDER BY SEQ