T-SQL uses CTE recursion

DECLARE @DiGui TABLE (id INT, ParentID int) INSERT into  @DiGui        (ID,          parentid         )        SELECT  1,                2        UNION        Select  2,                3        Union        Select  3,                4        Union        Select  5,                ten        Union        SELECT  1,                10SELECT  *from    @DiGui;    with temp ([id], [parentid]) as          (SELECT   ID,                        parentid               from     @DiGui               WHERE    [ ID] = 1               UNION all               SELECT   a.id,                        a.parentid               from     @DiGui a                        INNER JOIN temp on a.[id] = t Emp.[parentid]             )    SELECT  *    from    Temp

Results:

T-SQL uses CTE recursion