SQL Server CTE select single tree branch structure upto the root

Question

Is it possible to pass a parameter into a CTE that selects a node then selects its parent up to the root where the parentId is null?

In my code below if I pass in a parameter that selects Rain Coats and then recurses up the tree to mens wear where its parentId is null and selects all the nodes in that branch including children. Could someone help me with this please. My example just recurses and shows the depth

SQL example:

DECLARE @Department TABLE
(
    Id INT NOT NULL,
    Name varchar(50) NOT NULL,
    ParentId int NULL
)

INSERT INTO @Department SELECT 1, 'Toys', null
INSERT INTO @Department SELECT 2, 'Computers', null
INSERT INTO @Department SELECT 3, 'Consoles', 2
INSERT INTO @Department SELECT 4, 'PlayStation 3', 3
INSERT INTO @Department SELECT 5, 'Xbox 360', 2
INSERT INTO @Department SELECT 6, 'Games', 1
INSERT INTO @Department SELECT 7, 'Puzzles', 6
INSERT INTO @Department SELECT 8, 'Mens Wear', null
INSERT INTO @Department SELECT 9, 'Mens Clothing', 8
INSERT INTO @Department SELECT 10, 'Jackets', 9
INSERT INTO @Department SELECT 11, 'Rain Coats', 10

;WITH c 
AS
(
    SELECT Id, Name,1 AS Depth
    FROM @Department
    WHERE ParentId is null  

         UNION ALL

         SELECT t.Id, t.Name, c.Depth + 1 AS 'Level'
    FROM @Department T  
    JOIN c ON t.ParentId = c.Id

)
SELECT * FROM c WHERE c.Id = 3

Answer 1

Your current CTE just shows all the items in the tree, with their Depth and all the other properties. As such, it works fine.

To do what you're looking for, you have to almost "invert" the CTE - grab the item you're interested in first, as the "anchor" of your CTE, and then "recurse up" to the root:

DECLARE @StartID INT = 11

;WITH c 
AS
(
    SELECT Id, ParentId, Name, 1 AS Depth
    FROM @Department
    WHERE Id = @startID

    UNION ALL

    SELECT t.Id, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
    FROM @Department T  
    INNER JOIN c ON t.Id = c.ParentId
)
SELECT * 
FROM c 

This will do what you're looking for and output:

Id ParentId  Name            Depth
11    10     Rain Coats        1
10     9     Jackets           2
 9     8     Mens Clothing     3
 8   NULL    Mens Wear         4

Update

For a reverse order of Depth you can use this:

;WITH c 
AS
(
    SELECT Id, ParentId, Name, 1 AS Depth
    FROM @Department
    WHERE Id = @startID

    UNION ALL

    SELECT t.Id, t.ParentId, t.Name, c.Depth + 1 AS 'Level'
    FROM @Department T  
    INNER JOIN c ON t.Id = c.ParentId
)
SELECT Id,
       ParentID, 
       Name,
       MAX(Depth) OVER() - Depth + 1 AS InverseDepth
FROM c

Output from this:

Id ParentId  Name            InverseDepth
11    10     Rain Coats        4
10     9     Jackets           3
 9     8     Mens Clothing     2
 8   NULL    Mens Wear         1

Answer 2

Currently, your CTE has the root as its anchor, and goes from parent to child in its recursive part. If you want the whole tree, you'll need to start at the child of interest, and work your way up. This is one way to do that. I have introduced a new column StartingId which remains constant as we walk up the tree - this is what we will select based on:

;WITH c 
AS
(
    SELECT Id AS StartingId, Id, ParentId, Name, 0 AS Height
    FROM @Department

    UNION ALL

    SELECT c.StartingId, p.Id, p.ParentId, p.Name, c.Height + 1 AS Height
    FROM @Department p INNER JOIN c ON p.Id = c.ParentId
)
SELECT * FROM c WHERE c.StartingId = 11

gives

StartingId  Id          ParentId    Name                                   Height
----------- ----------- ----------- ----------------------------------------------
11          11          10          Rain Coats                             0
11          10          9           Jackets                                1
11          9           8           Mens Clothing                          2
11          8           NULL        Mens Wear                              3