CategoryID        ParentCategoryID     Name
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

CategoryID        Name          NameNavigation
1                     A                NULL
2                     B                A -> B
3                     C                A -> C
4                     D                A -> B -> D
5                     E                A -> B -> E 
6                     F                A -> C -> F
7                     G                A -> G
8                     H                A -> B -> E -> H
9                     I                 A -> G -> I
10                   J                 A -> B -> E -> H -> J

;with CategoryDrillDown AS
(select CategoryID, ParentCategoryID, Name, convert(nvarchar(1000),NULL) AS ParentName
from Category
where ParentCategoryID is null
union all
select Category.CategoryID, Category.ParentCategoryID, Category.Name, CategoryDrillDown.Name AS parentName
from Category
join CategoryDrillDown on CategoryDrillDown.CategoryID = Category.ParentCategoryID)
select * from CategoryDrillDown

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;