How To Get The Hierarchy Level Of This Query

I am trying to get the hierarchy level of this query. Here is the CTE recursive query WITH CategoryRec AS ( SELECT Id, Parentid, Name FROM dbo.Category UNION ALL

Solution 1:

You need to add a column called Level (or whatever you want to call it) - both to the "anchor" SELECT as well as the recursive part of your CTE - like this:

WITH CategoryRec AS 
(
    SELECT Id, Parentid, Name, 1AS'Level'FROM dbo.Category

    UNION ALL

    SELECT cr.Id, c.Parentid, cr.Name, cr.Level + 1FROM CategoryRec AS cr 
    INNER JOIN dbo.Category AS c ON cr.Parentid = c.Id
    WHERE c.Parentid ISNOT NULL
)
SELECTDISTINCT Id, Parentid, Name, Level
FROM  CategoryRec

Solution 2:

You need to select the top level of your hierarchy in the the first query with the level as marc_s wrote.

;WITH CategoryRec AS 
(
  SELECT a.Id, 
         a.Parentid, 
         a.Name, 
         1AS'Level'FROM dbo.Category as a
   WHERE a.Parentid IS NULL

  UNION ALL

  SELECT b.Id, 
         b.Parentid, 
         b.Name, 
         a.Level + 1FROM CategoryRec  as a
           INNER JOIN 
         dbo.Category as b on b.Parentid = a.Id
   WHERE b.Parentid ISNOT NULL
)
SELECT Id, Parentid, Name, Level
FROM  CategoryRec

Solution 3:

This returns the correct levels. But not sure if this query is performant is it should be

WITH CategoryRec AS 
(
    SELECT     Id, Parentid, Name, 1as [Level]
    FROM dbo.Category
    where Parentid IS NULL

    UNION ALL

    SELECT c.Id, c.Parentid, c.Name, (cr.[Level] + 1) as [Level]
    FROM Category c INNER JOIN
    CategoryRec AS cr ON c.Parentid = cr.Id
),
CategoryRec2 as
(
    SELECT Id, Parentid, Name, [Level]
    FROM CategoryRec

    UNION ALL

    SELECT cr.Id, c.Parentid, cr.Name, cr.[Level]
    FROM CategoryRec2 AS cr INNER JOIN
    CategoryRec AS c ON cr.Parentid = c.Id
    WHERE c.Parentid ISNOT NULL
)
SELECT Id, Parentid, Name, [Level]
FROM CategoryRec2