This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
ParentId should be NULL when there is no parent, not itself. That will make it much easier to query.
SELECT 1, 1010, NULL
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.
Updated. Pls check now
@Tom Phillips - Could you help me with the query?
I am not sure what you are looking for. If you look at the link I gave, it will give you ideas.
This maybe:
DROP TABLE IF EXISTS #temp;
CREATE TABLE #Temp (ID int, ParentID INT, [Name] VARCHAR(100) NULL)
INSERT INTO #Temp(ID, ParentID)
SELECT 1010, NULL UNION ALL
SELECT 11, 1010 UNION ALL
SELECT 12, 1010 UNION ALL
SELECT 13, 1010 UNION ALL
SELECT 14, 11 UNION ALL
SELECT 15, 11 UNION ALL
SELECT 16, 13 UNION ALL
SELECT 17, 1010 UNION ALL
SELECT 18, 1010
SELECT *
FROM #temp
ORDER BY Id,ParentId;
;WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
FROM #temp AS FirtGeneration
WHERE ParentId IS NULL
UNION ALL
SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
CAST(CASE WHEN Parent.Parents = ''
THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
END AS VARCHAR(MAX))
FROM #temp AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId
)
SELECT *
FROM Hierarchy
ORDER BY Parents
OPTION(MAXRECURSION 32767)
;
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.
Sure, Updated.
Hi @Kumar
Have you validate all the answers so far and provide any update?
If all of the answers are not working or helpful, please share with us your confusion or more details about this issue.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!