By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @IDs TABLE (
ID INTEGER
,PreviousID INTEGER
,Location INTEGER
)
INSERT INTO @IDs
SELECT 2,null,1235
UNION ALL SELECT 3,2,1236
UNION ALL SELECT 4,3,1239
UNION ALL SELECT 8,null,1237
UNION ALL SELECT 9,8,1234
UNION ALL SELECT 10,9,1235
Select * from @IDs
DECLARE @ORDERID Table (OrderID nvarchar (100))
Insert into @ORDERID values
('4')
,('9')
,('2')
;WITH q AS (
SELECT 0 lvl, ID, PreviousID,PreviousID LastId
,Location,Location as OriginalLocation
FROM @IDs
where ID in (select OrderID from @ORDERID)
UNION ALL
SELECT lvl+1, q.ID,u.PreviousId,q.PreviousId LastId
,q.Location,u.Location
FROM q
INNER JOIN @IDs u ON u.ID = q.PreviousID
)
-- select * from q order by id;
select lvl, ID, coalesce(LastId,Id) OriginalId,Location,OriginalLocation
| ID | PreviousID | Location |
|---|---|---|
| 2 | null | 1235 |
| 3 | 2 | 1236 |
| 4 | 3 | 1239 |
| 8 | null | 1237 |
| 9 | 8 | 1234 |
| 10 | 9 | 1235 |
| lvl | ID | OriginalId | Location | OriginalLocation |
|---|---|---|---|---|
| 0 | 2 | 2 | 1235 | 1235 |
| 2 | 4 | 2 | 1239 | 1235 |
| 1 | 9 | 8 | 1234 | 1237 |