SQL Server Grouped Number Generation

I need a way in a SQL Server query to generate grouped numbers when the data switches from one specific data value to another.

Based on the first two columns below and how they are ordered, how can the query generate the numbers in the third column?

When the Type column changes from Prim to anything else, all the records between the Prim record and the next Prim record, need to have the same number as the previous Prim record.

How can this be done in a query?

Answer

You can do this with a cumulative sum – simply count the number of 'Prim' values up to each row:

select t.*,
       sum(case when type = 'Prim' then 1 else 0 end) over (order by primarykey) as generatednumber
from t;