Modifying a query to show navigation path

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Archive: SQL Server 2005 Stored procedure concurrency problems in SQL Server 2005 SQL vs. T-SQL Database Snapshots: Creating read-only copies of a database Upgrading SQL Servers Determining number of servers needed for large member site SQL Server 2005 Enterprise Edition vs. Standard Edition Encryption failures when upgrading to SQL Server 2005 Upgrading to SQL Server 2005 while maintaining .adp access Viewing SQL Server 2005 data via Windows Forms SQL Server 2005 upgrade issues

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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