CTE parent-child showing siblings

Question

I have a CTE-query that displays a tree using recursion. This works great when displaying the whole tree. But I want to pass in ID as a variable and include the siblings for each current node.

Testcode:

DECLARE @TT TABLE 
(
ID int,
Name varchar(25),
ParentID int,
SortIndex int
)

INSERT @TT 
SELECT 1, 'A', NULL, 1 UNION ALL
SELECT 2, 'B_1', 3, 1 UNION ALL
SELECT 3, 'B', 1, 2 UNION ALL
SELECT 4, 'B_2', 3, 2 UNION ALL
SELECT 5, 'C', 1, 3 UNION ALL
SELECT 6, 'C_2', 5, 2  UNION ALL
SELECT 7, 'A_1', 1, 1 UNION ALL
SELECT 8, 'A_2', 1, 2 UNION ALL
SELECT 9, 'C_1', 5, 1 


;WITH CTETree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 1 AS Lev,
    CAST(ROW_NUMBER() OVER(ORDER BY SortIndex) AS VARBINARY(MAX)) AS SortPath
    FROM @TT
    WHERE ParentID IS NULL

    UNION ALL

    SELECT F.*, CTETree.Name AS ParentName, Lev + 1,
    SortPath + CAST(ROW_NUMBER() OVER(ORDER BY F.SortIndex) AS BINARY(32))
    FROM @TT AS F
    INNER JOIN CTETree
    ON F.ParentID = CTETree.ID 
)

SELECT * FROM CTETree
    ORDER BY SortPath

/*
DESIRED RESULT:

WHEN ID = 3 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
2   B_1 3   1   B   3
4   B_2 3   2   B   3
5   C   1   3   A   2

WHEN ID = 1 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
5   C   1   3   A   2

WHEN ID = 9 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
5   C   1   3   A   2
9   C_1 5   1   C   3
6   C_2 5   2   C   3

*/

SQL-Fiddle: http://sqlfiddle.com/#!3/d41d8/5526

Answer 1

Only in once I confront a question where disappear A_1 and A_2. Therefore add in exception(in case clause). If you need this records delete this expression AND f.ID != 7 AND f.ID != 8.

DECLARE @ID int = 3 -- variable wich you want pass
DECLARE @Matched int = (SELECT CASE WHEN ParentID = 1 THEN ID ELSE ParentID END FROM @TT WHERE ID = @ID)
;WITH CTETree
AS
(
 SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 1 AS Lev,
        CAST(ROW_NUMBER() OVER(ORDER BY SortIndex) AS VARBINARY(MAX)) AS SortPath,
        0 AS Matched
 FROM @TT
 WHERE ParentID IS NULL
 UNION ALL
 SELECT F.*, cte.Name AS ParentName, cte.Lev + 1,
        SortPath + CAST(ROW_NUMBER() OVER(ORDER BY F.SortIndex) AS BINARY(32)),
        CASE WHEN (cte.ID = 1 AND f.ID != 7 AND f.ID != 8)
        OR (cte.ID = @Matched AND cte.Lev + 1 > 2)
        THEN 1 END AS Matched
 FROM @TT AS F INNER JOIN CTETree cte
 ON F.ParentID = cte.ID
)
SELECT ID, Name, ParentID, SortIndex, ParentName, Lev FROM CTETree
WHERE Matched IS NOT NULL
ORDER BY SortPath

Demo on SQLFiddle