I have the following requirement,
Input
ID Parent_ID
------------
1 0
2 0
3 10
4 0
5 3
6 20
7 3
8 21
9 3
10 0
20 0
21 0
@ID = 1 the result has to be 1.@ID = 6 the result has to be 20.@ID = 5 or 7, 9 the result has to be 10, because 5 -> 3 (Parent_ID -> Str_ID) -> 10 (Parent_ID -> Str_ID) -> 0 (stop is 0 in Parent_ID), so the result 10.So my task is looking for ID as long as I'll find 0 in Parent_ID.
Output:
@ID Result
----------
1 1
2 2
3 10
4 4
5 10
6 20
7 10
8 21
9 10
10 10
20 20
21 21
A recursive CTE with rollup can do this: MSDN Article
BEGIN
--Setup some data
DECLARE @tmp as TABLE (ID int, ParentID int);
INSERT INTO @tmp
VALUES
(1 , 0 ),
(2 , 0 ),
(3 , 10),
(4 , 0 ),
(5 , 3 ),
(6 , 20),
(7 , 3 ),
(8 , 21),
(9 , 3 ),
(10, 0),
(20, 0),
(21, 0),
(44, 5),
(83, 44),
(46, 83),
(23, 7);
WITH Parents (ID, ParentID, TopParent) AS (
SELECT ID, ParentID, ID
FROM @tmp
WHERE ParentID = 0
UNION ALL
SELECT t.ID, t.ParentID, p.TopParent
FROM Parents p
JOIN @tmp t on t.ParentID = p.id)
SELECT * FROM Parents
--Or to get just the ID and top parent: SELECT ID, TopParent FROM Parents
END
Results:
ID ParentID TopParent
----------- ----------- -----------
1 0 1
2 0 2
4 0 4
10 0 10
20 0 20
21 0 21
8 21 21
6 20 20
3 10 10
5 3 10
7 3 10
9 3 10
23 7 10
44 5 10
83 44 10
46 83 10