row ID reset based on Value

Question

I need to generate an ID based store and reset anytime the transaction type = 200. Below is an example for my data.

StoreID     TransactionID   
A               200 
A               300
A               45
A               1
A               200
A               23
A               2000
A               200
A               1   

Now, I need this to be populated. Row number that resets everytime the 200 is shown. One transaction can have multiple ids and also different numbers of transactions in between. However 200 is BeginingOftransaction so I need to to be able to get an ID and reset on the 200.

Rn  StoreID     TransactionID   
1   A               200 
2   A               300
3   A               45
4   A               1
1   A               200
2   A               23
3   A               2000
1   A               200
2   A               1   

Answer 1

This is for SQL Server:

Hope this is exactly what you want. Try this:

DECLARE @Tab TABLE(StoreID VARCHAR(1),id int)

INSERT INTO @Tab VALUES('A',200),('A',89),('A',89),('A',98),('A',200),('A',1),('A',3),('A',200)

SELECT SUM(1) OVER(PARTITION BY D.RES ORDER BY RN)RN,StoreID,ID
from(
    SELECT id,StoreID, RN
        ,SUM(CASE WHEN id=200 THEN 1 ELSE 0 END) OVER(ORDER BY (SELECT RN))Res
    FROM (
        SELECT id,StoreID,ROW_NUMBER() OVER(ORDER BY(SELECT NULL))RN
        FROM @Tab)D
    )D

And the result is:

RN  StoreID ID
1   A       200
2   A       89
3   A       89
4   A       98
1   A       200
2   A       1
3   A       3
1   A       200