CREATE TABLE #x ( ID CHAR(1), ParentID CHAR(1) )
INSERT #x (ID, ParentID) SELECT A , NULL UNION ALL SELECT B , A UNION ALL SELECT C , A UNION ALL SELECT D , B UNION ALL SELECT E , C
.WITH FullPath AS ( SELECT ID, 1 AS Level, CONVERT(VARCHAR(MAX), ID) AS Path FROM #x WHERE ParentID IS NULL
UNION ALL
SELECT x.ID, FP.Level 1 AS Level, FP.Path . x.ID AS Path FROM #x AS x JOIN FullPath FP ON x.ParentID = FP.ID ) SELECT * FROM FullPath |