SQL Server CTE Recursion - Tracing a tree back to a certain tree level for each node

I have two tables, Item (ItemID, CatID, Name) and Category (CatID, ParentID, Name, CatLevel). Category is a tree hierarchy of categories that items can be in. The table also tracks what category level each node is in. Here's an example:

1. Animal (CatLevel = 1)
    2. Dog (CatLevel = 2)
        3. Beagle (CatLevel = 3)
        4. Labrador (CatLevel = 3)
            5. Yellow (CatLevel = 4)
            6. Chocolate (CatLevel = 4)
            7. Black (CatLevel = 4)
    8. Cat (CatLevel = 2)
        9. Tabby (CatLevel = 3)
    10. Horse (CatLevel = 2)
        11. Stallion (CatLevel = 3)

I'm trying to come up with a query that gives me a list of items with their regular CatID and a CatID at a level I specify (for example, level 2). Example output:

Item      CatID    LevelTwoCatID
______    _____    _____________
Dog       2        2
Beagle    3 开发者_高级运维       2
Labrador  4        2
Yellow    5        2
Chocolate 6        2
Black     7        2
Cat       8        8
Tabby     9        8
Horse     10       10
Stallion  11       10

I know how to trace back up to the category of a single node, but I don't know how to make a more generic query that will provide the "LevelTwoCatID" for every node. Here's how I'd do it for one:

WITH Tree AS
(
    SELECT * FROM Category WHERE CatID = 6 -- Chocolate lab, change for some other

    UNION ALL

    SELECT * FROM Category AS a
    INNER JOIN Tree t ON a.CatID = t.ParentID
)
SELECT * FROM Tree WHERE CatLevel = 2

How can I get the CatLevel = 2 node ID for all nodes/leaves?

Thanks!

I know the post is quite old, but here is how I did that.

In my table there are several root categories, therefore I use @RootId to specify which one I am after. Root category has ParentID set to NULL.

If you do not have CatLevel column in your categories table - cte Tree creates it for you. If you are going to use the query frequently, it is better to add it to the table (UPDATE code is commented in the end).

If you wish to have dynamic number of columns (not fixed to 5 as in my case), make a dynamic SQL query for it.

DECLARE @RootId INT, @SearchId INT
SET @RootId = 0 
SET @SearchId = 0

--SELECT it.Id, it.ParentId, it.Name, it.RootId FROM ItemTree it 
--WHERE (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)

;WITH Tree AS --gets category levels
(
  SELECT 1 as CatLevel, it.Id, it.ParentId, it.Name, it.RootId
  FROM ItemTree it WHERE it.ParentId IS NULL AND (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)

  UNION ALL

  SELECT t.CatLevel + 1 as CatLevel, it.Id, it.ParentId, it.Name, it.RootId
  FROM ItemTree it
  INNER JOIN Tree t ON it.ParentId = t.Id
  WHERE (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)

  ----if CatLevel already exists in the table, just use it instead for better performance
  --SELECT it.CatLevel, it.Id, it.ParentId, it.Name FROM ItemTree it 
  --WHERE (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)
)
, LeafIds AS --use it to show only leaf categories
(
  SELECT DISTINCT Id FROM ItemTree it WHERE (@RootId = 0 OR it.RootId = @RootId)
  EXCEPT
  SELECT DISTINCT ParentId FROM ItemTree it WHERE (@RootId = 0 OR it.RootId = @RootId)
)
, LeafTree AS --prepare data for pivots
(
  SELECT t.CatLevel, t.Id, t.ParentId, t.Name, Id AS LeafId
  FROM Tree t --WHERE Id IN (SELECT Id FROM LeafIds)

  UNION ALL

  SELECT t.CatLevel, t.Id, t.ParentId, t.Name, lt.LeafId
  FROM Tree t
  INNER JOIN LeafTree lt ON lt.ParentId = t.Id
)
, TreeFinal AS --make pivots for Id and Name over same leafIds
(
  SELECT t.*, pt1.[1] AS ID_1, pt1.[2] AS ID_2, pt1.[3] AS ID_3, pt1.[4] AS ID_4, pt1.[5] AS ID_5
  , pt2.[1] AS Name_1, pt2.[2] AS Name_2, pt2.[3] AS Name_3, pt2.[4] AS Name_4, pt2.[5] AS Name_5
  , ISNULL(pt2.[1], '') + ISNULL(' - ' + pt2.[2], '') + ISNULL(' - ' + pt2.[3], '') + ISNULL(' - ' + pt2.[4], '') + ISNULL(' - ' + pt2.[5], '')  AS Path
  FROM Tree t

  JOIN (
    SELECT LeafId, CatLevel, Id
    FROM LeafTree
  ) source
  PIVOT ( MAX(Id) FOR CatLevel IN ([1], [2], [3], [4], [5]) ) AS pt1 ON pt1.LeafId = t.Id

  JOIN (
    SELECT LeafId, CatLevel, Name
    FROM LeafTree
  ) source
  PIVOT ( MAX(Name) FOR CatLevel IN ([1], [2], [3], [4], [5]) ) AS pt2 ON pt2.LeafId = t.Id
) 
--Use following to update CatLevel in your table, if it was not there before
--UPDATE it SET CatLevel = Tree.CatLevel FROM Tree JOIN dbo.ItemTree it ON it.Id = Tree.Id
SELECT * FROM TreeFinal ORDER BY Path

Here's screenshot of the before and after queries