I have a dataset in which i am matching rows together using different methods. Some of the results of these methods overlap. I am trying to use a recursive query to group lines that do overlap. Here is a sample of the data table.

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)