Q&A for those committed to a vegan or vegetarian lifestyle and anyone interested in learning more
declare @T table(ID int, ParentID int, DATA char(1)); insert into @T values (1, -1, 'a'), (2, 1, 'b'), (3, 2, 'c'), (4, 3, 'd'), (5, 3, 'f'); declare @ID int = 2; with C as ( select ID, ParentID, DATA from @T where ID = @ID union all select T.ID, T.ParentID, T.DATA from @T as T inner join C on T.ParentID = C.ID ) select ID, ParentID, DATA from C