SQL Partition By alternating groups of rows, Sql Server

Language: Layout:

create table t (
    tKey int
  , LotId char(1) 
  , TransactionType varchar(32)
  , Quantity int
  , Destination varchar(32)
  )
insert into t values
 (1,'A','Transform',NULL,'Foo')
,(2,'A','Transform',NULL,'Bar')
,(3,'A','Consume',100,NULL)
,(4,'B','Transform',NULL,'Bob')
,(5,'B','Transform',NULL,'Fred')
,(6,'B','Consume',75,NULL)
,(7,'B','Consume',50,NULL)
,(8,'B','Transform',NULL,'Sally')
,(9,'B','Transform',NULL,'Fred')
,(10,'B','Consume',60,NULL)
,(11,'C','Transform',NULL,'Bar')
,(12,'C','Transform',NULL,'Fred')
,(13,'C','Consume',25,NULL)

;with cte as (
  select *
    , PrevTransactionType = isnull(lag(TransactionType) over (order by tKey),'Consume')
  from t
)
select t.tKey, t.LotId, t.TransactionType, t.Quantity, t.Destination
 , Grouping = LotId + convert(varchar(10),dense_rank() over (partition by LotId order by GroupNumber))
from cte as t
outer apply (
  select top 1 
    GroupNumber = i.tKey
    from cte as i
    where i.tKey <= t.tKey
      and i.TransactionType = 'Transform'
      and i.PrevTransactionType = 'Consume'
    order by i.tKey desc
    ) x

View schema
Execution time: 0,02 sec, rows selected: 13, rows affected: 13, absolute service time: 0,19 sec