Find which stores have been transferred to which store that is currently active

I have a stores table which takes in storeid, isactive and transferstoreid. id= the id of the store isactive= whether the store (id column) is active or not transferstoreid = the new store id to which the previous store would be transferred to if isactive=0

I want to identify all the stores and their ACTIVE parents. IF a store is active i.e. 1, then transferstoreid is NULL and the id would directly be the parent. However, there might be a case that store 1 gets transferred to store 2 which then gets transferred to store 3 till .....n.

How do I get a list of all the stores and their currently active transferred stores (or parent)

For example, if store 10 got closed(isactive=0) and was transferred to store 14 but then 14 got closed (isactive=0) later and transferred to 20 and then 20 also got closed(isactive=0) and transferred to 25 (isactive=1); hence, for store 10,14,20 -> the parent/active store would be 25 and for store 25 the parent would be 25 itself (id)

Here's the sample data :

-- Create the store table
CREATE TABLE store (
    ID INT,
    ISACTIVE INT,
    TRANSFERTONEXTSTOREID INT
);

-- Insert data into the store table
INSERT INTO store (ID, ISACTIVE, TRANSFERTONEXTSTOREID)
VALUES 
    (54, 0, 77),
    (101, 0, 120),
    (10, 0, 14),
    (77, 1, NULL),
    (40, 0, 99),
    (99, 0, 101),
    (12, 1, NULL),
    (37, 0, 54),
    (20, 0, 25),
    (60, 1, NULL),
    (38, 1, NULL),
    (120, 1, NULL),
    (14, 0, 20),
    (25, 1, NULL),
    (11, 0, 12);

I'm easily able to do this via hierarchical query ( connect by root and path). I have used this query in Oracle Apex

    SELECT CONNECT_BY_ROOT ID AS store_id, id as current_id
    FROM store
    WHERE ISACTIVE = 1
    CONNECT BY PRIOR TRANSFERTONEXTSTOREID = ID;

AND got this output :

STORE_ID    CURRENT_ID
 10          25
 11          12
 12          12
 14          25
 20          25
 25          25
 37          77
 38          38
 40          120
 54          77
 60          60
 77          77
 99          120
 101         120
 120         120

How do I do this in SQL Server

Any leads would be greatly appreciated.

First, take all rows with isactive=1 as anchor for recursion. End points for other rows are rows with isactive=1.

Next level - rows, linked thru transfertonextstoreid to anchor rows.
And we save id for anchor row.

This way, we don't generate extra rows in the recursion.
If we go from the opposite side and look for the root row, the intermediate steps may be superfluous and they will have to be filtered out.

step 1 row  (25, 1, NULL)              =output (25, 1, NULL)
step 2 row  (20, 0, 25)->(25, 1, NULL) =output (20, 0, 25)
step 3 row  (14, 0, 20)->(20, 0, 25)   =output (14, 0, 25)
step 4 row  (10, 0, 14)->(14, 0, 25)   =output (10, 0, 25)

See example

with rec_cte as ( -- anchor - rows with isactive=1
    select s.id, s.id as current_id,
       s.isactive,
       1 as reclevel
    from store s 
    where isactive=1

    union all
                  -- carrent_id is not changed, saved from anchor
    select s.id, r.current_id as current_id,
       s.isactive,
       r.reclevel + 1 
    from rec_cte r
    join  store s on s.transfertonextstoreid = r.id 
)
select *
from rec_cte
order by id, reclevel;

Fiddle