Find top parent in hierarchy

Question

I have a SQL table with different project codes. A project can consist of multiple levels (sub-projects). I want to find the top level (top parent) for each of the project codes using SQL coding.

My table looks like this:

CREATE TABLE #Temp
(
    Child nvarchar(100),
    Parent nvarchar(100)

)

insert into #Temp (Child,Parent)
values
('17210',Null),
('17210-100','17210'),
('17210-100-100','17210-100'),
('1310',Null),
('1310-100','1310'),
('1310-100-100','1310-100')
;

select * from #Temp

If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End

This is the result I want:

Child           Parent      TopParent
17210           NULL        17210
17210-100       17210       17210
17210-100-100   17210-100   17210
1310            NULL        1310
1310-100        1310        1310
1310-100-100    1310-100    1310

How do I find the top parent for each project number (child)? I have an idea that using recursive CTE's might be the solution, but I am not sure if this is the right approach.

Any help is much appreciated.

Answer 1

I'm not really sure how it's different from any other recursive cte question, but since you've done such a great job posting sample data as DDL+DML I've decided to answer the question anyway (though it could have been just as easily closed as a duplicate).

As you wrote, using a recursive cte is the solution here:

WITH cte AS 
(
    SELECT Child, Parent, Child As TopParent
    FROM #Temp
    WHERE Parent IS NULL
    UNION ALL
    SELECT T.Child, T.Parent, cte.TopParent
    FROM #Temp As T
    JOIN cte 
        ON T.Parent = cte.Child
)

SELECT *
FROM cte
ORDER BY TopParent DESC, Parent;