Geek Answers Handbook

SQL Server CTE selects a single tree branch structure to the root

Is it possible to pass a parameter to the CTE, which selects node and then selects the parent to the root, where parentIdit is null?

In my code below, if I pass in a parameter that Rain Coats selects and then recurses the tree to the male carry, where it parentIdis null and selects all the nodes in this branch, including the child ones. Can someone help me with this please. My example just repeats 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
+5
source share
2 answers

CTE Depth . , .

, , "" CTE - , "" CTE, "recurse up"

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

, :

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

:

;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

:

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

CTE . , , , . . StartingId, , - , :

;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


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
+4

More articles:


All Articles