Merging Rows containing ColumnA predicated by next result(s) of ColumnA

I want the result to display the amount of instances a particular service has been booked for.

If the following row has the same service then they should be summed together.

Sample Data

Expected Output

I have currently implemented a Cursor based solution which works fine for my dataset but the code is cumbersome.

Can someone suggest a set based solution to solve this problem?

Answer

Are you just looking for GROUP BY?

select text, count(*)
from t
group by text;

EDIT:

This is a gaps-and-islands problem. A simple method uses the difference of row numbers:

select text, count(*)
from (select t.*,
             row_number() over (order by rn) as seqnum,
             row_number() over (partition by text order by rn) as seqnum_t
      from t
     ) t
group by text, (seqnum - seqnum_t);

The difference of row numbers defines the grouping you want. This is a little cumbersome to explain, but if you run the subquery, you will probably see that this works.