I have a stores table which takes in storeid, isactive and transferstoreid. I want to identify all the stores and their ACTIVE parents. IF I store is active i.e. 1, then transferstoreid=0 and there might be a case that store 1 gets transferred to store 2 which then gets transferred to store 3 til …..n. How do I get a list of all the stores and their currently active transferred stores (or parent)

Here’s the sample data :

-- Create the store table
CREATE TABLE store (
    id INT,
    isactive varchar(20),
    transfertonextstoreid INT
);
`
`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),
    (14,1,NULL);

**Required 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

Any leads would be greatly appreciated. Please solve it via sql server 😀

thanks

I’m easily able to do this via hierarchical query ( connect by root and path) but I want to do this in SQL Server,
did try the below but it fails , as in doesn’t loop through completely and gives the correct output

with rec_cte AS (
    select
    s.id,
    s.transfertonextstoreid as current_id,
    s.isactive,
    1 as reclevel
    FROM 
    store s 
    UNION ALL 
    select 
    s.id,
    s.transfertonextstoreid as current_id,
    s.isactive,
    r.reclevel + 1 
    FROM 
    store s 
    JOIN rec_cte r 
    ON
    s.transfertonextstoreid = r.id 
    WHERE 
    (s.isactive=0 AND s.transfertonextstoreid IS NOT NULL) OR (s.isactive=1)
)
SELECT * ,
CASE WHEN isactive=1 THEN id
WHEN isactive=0 AND current_id IS NOT NULL AND reclevel=MAX(reclevel) OVER () THEN current_id
END AS newids
FROM 
rec_cte ;

This is the required output:

**Required 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