SQL Server: How to get top parent id and last child id from parent child hierarchy

Hi，@T.Zacks
It is a good way to use recursive CTE to get the parent child hierarchy.
Please check this:

CREATE TABLE #table(ID INT,ParentID INT,Name VARCHAR(50))  
INSERT INTO #table VALUES(1, 0,' Model'),(2, 1,'Consensus Model '),(3, 1,'Segment Details'),(4, 3,'Key Financials')  
  
--Query from parent to child  
;WITH SubsCTE AS  
	(  
	 --Anchor Member (Return the row where the employee is entered)  
	 select  ID,ParentID,Name,0 as lvl  
	 from #table   
	 where ID = 1  
	 UNION ALL  
	 --Recursive Member (Return recursive members of subordinates)  
	 select C.ID,C.ParentID,C.Name,P.lvl + 1  
	 from SubsCTE P JOIN #table C   
	      ON C.ParentID = P.ID  
	)  
SELECT * FROM SubsCTE  
  
--Query from child to parent   
;WITH MgrsCTE AS  
	(  
	 select ID,ParentID,Name,0 as lvl  
	 from #table   
	 where ID = 3  
	 UNION ALL  
	 select P.ID,P.ParentID,P.Name,C.lvl + 1  
	 from MgrsCTE C JOIN #table P   
	      ON C.ParentID = P.ID  
	)  
SELECT * FROM MgrsCTE  

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.