Modifying a query to show navigation path

I viewed your article on the Common Table Expression that will recursively query itself. How can I modify this query to show the navigation path down the tree to each node returned in the result set, in a different format? For example, if the data is:

ID ParentId
A null
B A
C A
D B
E C

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

    Requires Free Membership to View

    Login 

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

This was first published in June 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top