WITH Hierarchy
AS
(
SELECT Id, CategoryName,ParentId, Cast(CategoryName as nvarchar(Max)) as hierarchy
FROM invCategoryHead AS FirtGeneration
WHERE ParentId IS NULL Or ParentId=0
UNION ALL
SELECT NextGeneration.Id, NextGeneration.CategoryName, NextGeneration.ParentId ,
Parent.hierarchy + ' -> ' + Cast(NextGeneration.CategoryName as Nvarchar(Max)) as hierarchy
FROM invCategoryHead AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.Id
)
SELECT P.*
FROM
Hierarchy P
WHERE P.Id IN
(
SELECT H.ParentId
FROM Hierarchy H
)
UNION
SELECT *
FROM Hierarchy
Order by ParentId Asc
How get list of Category with Parent Name ( Recursive Loop)