Recursive CTE to get Grandparent, parent, children I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
900107
    --&gt; 200237
            --&gt; 100112
            --&gt; 100113
    --&gt; 200238
            --&gt; 100114
            --&gt; 100115
                --&gt; 100120
                    --&gt; 100001
                    --&gt; 100002
--&gt; 200239
--&gt; 200501
--&gt; 300112
--&gt; 400987
--&gt; 444444

In this example, 900107, 200237, 200238, 100120 are all parents where 900107 is going to be our grandparent.
Here's how it looks in the table:
ParentPart   Component
900107       200237
900107       200238
900107       200239
900107       200501
900107       300112
900107       400987
900107       444444
200237       100112
200237       100113
200238       100114
200238       100115
100120       100001
100120       100002    

I have a recursive CTE that will get me all of the parents and children, but not the &quot;grandkids&quot; or &quot;great grandkids&quot; if they exist.
Here's my CTE:
CREATE TABLE #Hierarchy (
ParentPart NVARCHAR(6),
Component NVARCHAR(6))

INSERT INTO #Hierarchy (ParentPart, Component)
VALUES ('900107', '200237'),
       ('900107', '200238'),
       ('900107', '200239'),
       ('900107', '200501'),
       ('900107', '300112'),
       ('900107', '400987'),
       ('900107', '444444'),
       ('200237', '100112'),
       ('200237', '100113'),
       ('200238', '100114'),
       ('200238', '100115'),
       ('100115', '100120'),
       ('100120', '100001'),
       ('100120', '100002') 

;WITH hier AS (
SELECT ParentPart AS MainPart, ParentPart, Component
  FROM #Hierarchy
UNION ALL
SELECT hier.MainPart, p.ParentPart, p.Component
  FROM hier 
  JOIN #Hierarchy AS p
    ON p.Component = hier.ParentPart
)

SELECT ParentPart
     , Component
  FROM hier    
ORDER BY ParentPart, Component

OPTION (MAXRECURSION 0)

This works for the most part....I can get this if I use one ParentPart. We have thousands of ParentParts and we'd need to know who the grandparent is. Ideally we'd want the output to look like this where the parent part is the grandparent and every child and grand child is in the component field
ParentPart    Component
900107        200237
900107        200238
900107        200239
900107        200501
900107        300112
900107        400987
900107        444444
900107        100120
900107        100112
900107        100113
900107        100114
900107        100115
900107        100001
900107        100002

What would be the best way to do this?
I believe this CTE can give you the desired result:
;WITH hier AS 
(

    SELECT DISTINCT h1.ParentPart, h1.Component, 
        h1.ParentPart AS GrandParent
    FROM #Hierarchy h1
        LEFT JOIN #Hierarchy h2 ON h1.ParentPart = h2.Component
    WHERE h2.Component IS NULL

    UNION ALL

    SELECT h1.ParentPart, h1.Component, h.GrandParent
    FROM #Hierarchy h1
        INNER JOIN hier h ON h.Component = h1.ParentPart 

)
SELECT GrandParent, Component 
FROM hier
ORDER BY GrandParent, Component;

Since you're only interested on the highest parent in each relationship chain, I used the trick of adding a third column on the anchor member containing what would be the great-great-grandparents. With that I can relate what value was the grandparent for each level of the chain no matter how many levels I descend.