WITH Categories (CategoryID, ParentCategoryID, CategoryName, Level)
AS
(
-- Anchor member definition
    SELECT e.CategoryID, e.ParentCategoryID, e.CategoryName,
        0 AS Level
    FROM Categories AS e
    UNION ALL
-- Recursive member definition
    SELECT e.CategoryID, e.ParentCategoryID, e.CategoryName,
        Level + 1
    FROM Categories AS e

quote:

---

Originally posted by JoeJack222

So far I have

WITH Categories (CategoryID, ParentCategoryID, CategoryName, Level)
AS
(
-- Anchor member definition
    SELECT e.CategoryID, e.ParentCategoryID, e.CategoryName,
        0 AS Level
    FROM Categories AS e
    UNION ALL
-- Recursive member definition
    SELECT e.CategoryID, e.ParentCategoryID, e.CategoryName,
        Level + 1
    FROM Categories AS e

I get "Incorrect syntax near 'e'." which is for my second e.
Any help?

---

WITH Categories_CTE (CategoryID, ParentCategoryID, CategoryName, Level)
AS
(
-- Anchor member definition
    SELECT e.CategoryID, e.ParentCategoryID, e.CategoryName,
        0 AS Level
    FROM Categories AS e
    WHERE e.ParentCategoryID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT c.CategoryID, c.ParentCategoryID, c.CategoryName,
        cc.Level + 1
    FROM Categories AS c
    INNER JOIN Categories_CTE cc
    ON cc.CategoryID= c.ParentCategoryID
)
SELECT CategoryID, ParentCategoryID, CategoryName, Level
FROM Categories_CTE 
OPTION (MAXRECURSION 0)