SQL Server CTE -Find top parentID forEach childID?

Question

I have a table which contains hierarchy data - something like: ``` childID | parentID ____________________ 1 | 5 5 | 9 9 | 20 2 | 4 3 | 7 7 | 8 8 | 8 20 | 20 4 | 4 8 | 8 ``` desired output: I've created a recursive CTE which finds me the top `fatherID`. Something like: ``` ;WITH cte AS ( SELECT a.childID ,a.parentID ,1 AS lvl FROM [Agent_Agents] a WHERE a.childID = 214 //<==== value to begin with !! - thats part the problem UNION ALL SELECT tmp.childID ,tmp.parentID ,cte.lvl+1 FROM [Agent_Agents] tmp INNER JOIN cte ON tmp.childID = cte.parentID WHERE cte.childID<>cte.parentID ) SELECT * FROM cte WHERE lvl = ( SELECT MAX(lvl) FROM cte ) ``` The problem: I executed the CTE with explicit `childID` value to begin with (214) ! So it gives me the value for 214 only. the CTE do the recursive part and find topParent for childID. but I want `ForEach row in the Table` - to execute the CTE with the `childID` value ! I have tried to do it with `CROSS APPLY`: Something like: ``` select * from myTa…

Accepted Answer

Not sure I understand what you are looking for but it could be this.

```
;WITH c 
     AS (SELECT childid, 
                parentid, 
                parentid AS topParentID 
         FROM   @myTable 
         WHERE  childid = parentid 
         UNION ALL 
         SELECT T.childid, 
                T.parentid, 
                c.topparentid 
         FROM   @myTable AS T 
                INNER JOIN c 
                        ON T.parentid = c.childid 
         WHERE  T.childid <> T.parentid) 
SELECT childid, 
       topparentid 
FROM   c 
ORDER  BY childid 
```

SE-Data

It is the same as answer by marc_s with the difference that I use your table variable and the fact that you have `childID = parentID` for root nodes where the answer by marc_s has `parent_ID = null` for root nodes. In my opinion it is better to have `parent_ID = null` for root nodes.

SQL Server CTE -Find top parentID forEach childID?

Solution

Problem