Have you tried using a recursive CTE

e.g.

;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