SQL Server: Run separate CTE for each record without cursor or function

Question

Given table A with a column LocationID and many records,

Is it possible to fully run a CTE for each record, without using a cursor (while fetch loop) or function (via cross apply)?

I can't run a CTE from the table A because the CTE will go deep through a parent-child hierarchical table (ParentID, ChildID) to find all descendants of a specific type, for each LocationID of table A. It seems that if I do CTE using table A, it will mix the children of all LocationID in table A.

Basically I need to separately run a CTE, for each LocationID of table A, and put in a table with LocationID and ChildID columns (LocationID are the ones from table A and ChildID are all descendants of a specific type found via CTE).

Answer 1

I was able to find a solution. I just had to keep the original LocationID as a reference, then in the CTE results, which will include all possible records as it goes deep into the list, I apply the filter I need. Yes, all records are mixed in the results, however because the reference to origin table's LocationID was kept (as OriginalParentID) I'm still able to retrieve it.

;WITH CTE AS
(
    --Original list of parents
    SELECT a.LocationID AS OriginalParentID, l.ParentID, l.ChildID, l.ChildType
    FROM TableA a
    INNER JOIN tblLocationHierarchy l ON l.ParentID = a.LocationID

    UNION ALL

    --Getting all descendants of the original list of parents
    SELECT CTE.OriginalParentID, l.ParentID, l.ChildID, l.ChildType
    FROM tblLocationHierarchy l
    INNER JOIN CTE ON CTE.ChildID = l.ParentID
)
SELECT OriginalParentID, ChildID
FROM CTE
--Filtering is done here
WHERE ChildType = ...