Q&A for professional and amateur woodworkers
DECLARE @myTable TABLE (childID INT ,parentID INT ,NAME NVARCHAR(30),location int); INSERT INTO @myTable SELECT 1, 1, 'a',1 UNION ALL SELECT 2, 1, 'b',1 UNION ALL SELECT 3, 2, 'c',2 UNION ALL SELECT 5, 1, 'e',2 UNION ALL SELECT 6, 6, 'f',2 UNION ALL SELECT 7, 6, 'r1',0 UNION ALL SELECT 4, 2, 'd',1 ; WITH CTE AS ( SELECT childID ,parentID, 0 AS depth,NAME , location FROM @myTable WHERE childID = parentID UNION ALL SELECT top 100 percent TBL.childID ,TBL.parentID, CTE.depth + 1 , TBL.name ,TBL.location FROM @myTable AS TBL INNER JOIN CTE ON TBL.parentID = CTE.childID WHERE TBL.childID<>TBL.parentID ) SELECT depth,childID,parentID,location,REPLICATE('----', depth) + name FROM CTE ORDER BY childid