Q
Problem solve Get help with specific problems with your technologies, process and projects.

Modifying a query to show navigation path

SQL Server 2005 expert Adam Machanic offers code to modify a query showing a navigation path down a tree to each node returned in the original result, but this time in a different format.

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

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close