By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select
* into #temp
from
(
values
('id100', 'status1', 1),
('id100', 'status2', 2),
('id100', 'status1', 3),
('id100', 'status0', 4),
('id100', 'status2', 5),
('id100', 'status2', 6),
('id100', 'status1', 7),
('id100', 'status1', 8),
('id100', 'status2', 9),
('id101', 'status1', 10),
('id101', 'status2', 11)
) t(id, status, rowNum)
11 rows affected
select * ,
case when status = 'status2' then (
select top(1) Cast(rowNum as varchar(2))
from #temp t2
where t2.id = t.id and t2.status= 'status1' and t2.rowNum < t.rowNum
order by rowNum desc
)
else '' end [Value]
from #temp t
order by id, rowNum;
| id | status | rowNum | Value |
|---|---|---|---|
| id100 | status1 | 1 | |
| id100 | status2 | 2 | 1 |
| id100 | status1 | 3 | |
| id100 | status0 | 4 | |
| id100 | status2 | 5 | 3 |
| id100 | status2 | 6 | 3 |
| id100 | status1 | 7 | |
| id100 | status1 | 8 | |
| id100 | status2 | 9 | 8 |
| id101 | status1 | 10 | |
| id101 | status2 | 11 | 10 |