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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With