DECLARE @Category TABLE(CategoryID int,ParentCategoryID int, Name nvarchar(1000))

INSERT INTO @Category(CategoryID,ParentCategoryID,Name)

VALUES

(1 , NULL, 'A' ),

(2 , 1 , 'B' ),

(3 , 1 , 'C' ),

(4 , 2 , 'D' ),

(5 , 2 , 'E' ),

(6 , 3 , 'F' ),

(7 , 1 , 'G' ),

(8 , 5 , 'H' ),

(9 , 7 , 'I' ),

(10, 8 , 'J' );

WITH Recur AS (

SELECT CategoryID, ParentCategoryID, Name, Name AS NameNavigation

FROM @Category

UNION ALL

SELECT r.CategoryID, c.ParentCategoryID, r.Name, CAST(c.Name + N' -> ' + r.NameNavigation AS nvarchar(1000))

FROM @Category c

INNER JOIN Recur r ON r.ParentCategoryID = c.CategoryID

)

SELECT CategoryID,Name,NameNavigation

FROM Recur

WHERE ParentCategoryID IS NULL

ORDER BY CategoryID;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537