Looking for SQL Query - Parent Child Data

Question

I have data like below, just an example:

CREATE TABLE #Temp (ID int, ChildID INT, ParentID INT)  
  
INSERT INTO #Temp  
  
SELECT 1, 1010, NULL UNION ALL  
SELECT 2, 11, 1010  UNION ALL  
SELECT 3, 12, 1010  UNION ALL  
SELECT 4, 13, 1010  UNION ALL  
SELECT 5, 14, 11   UNION ALL  
SELECT 6, 15, 11   UNION ALL  
SELECT 7, 16, 13   UNION ALL  
SELECT 8, 17, 1010  UNION ALL  
SELECT 9, 18, 1010  
  
SELECT * FROM #Temp t  

I need to find few examples where a ParentID has Childs and a Child out of that list again has Childs and so on.

Answer 1

See:
https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child

Your sample data does not include any records for the parent 1010.

Answer 2

Hi @Kumar
If you want to show the hierarchy path, then check this：

;WITH CTE AS  
( --Anchor member  
  SELECT ChildID,ParentId,1 AS [Level],CAST((ChildID) AS VARCHAR(MAX)) AS Hierarchy  
  FROM #Temp t1  
  WHERE ParentId IS NULL  
  UNION ALL  
  --Recursive member       
  SELECT t2.ChildID,t2.ParentID,C.[level] + 1 AS [Level],CAST((C.Hierarchy + '->' + CAST(t2.ChildID AS VARCHAR(10))) AS VARCHAR(MAX)) AS Hierarchy  
  FROM #Temp t2 JOIN CTE C ON t2.ParentId = C.ChildID     
)  
SELECT * FROM CTE  

Output:

Refer to this blog for more details: CTE RECURSIVE QUERY TO GET PARENT CHILD HIERARCHY WITH LEVELS

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.