By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
I want the result from your query to be:
ID Level Path
A 1 A
B 2 A.B
C 2 A.C
D 3 A.B.D
E 3 A.B.C
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
Dig Deeper on SQL Server Database Modeling and Design
Related Q&A from Adam Machanic
Database Snapshots feature in SQL Server 2005 Enterprise Edition creates read-only files. For these separate databases, our site expert Adam Machanic...continue reading
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ...continue reading
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.