Get rows in all levels with parentId SQL

Question

I have a table Categories, and each row in this tables have a parentId. The parent is just an other row in the same table.

I want to create query to get all children in different levels by just giving the Id of the first Parent.

Answer 1

With recursive cte:

DECLARE @t TABLE ( id INT, pid INT )
INSERT  INTO @t
VALUES  ( 1, NULL ),
        ( 2, NULL ),
        ( 3, 1 ),
        ( 4, 1 ),
        ( 5, 3 ),
        ( 6, 5 ),
        ( 7, 6 ),
        ( 8, 6 )

DECLARE @p INT = 1;

WITH    cte
          AS ( SELECT   *
               FROM     @t
               WHERE    pid = @p
               UNION ALL
               SELECT   t.*
               FROM     @t t
                        JOIN cte c ON c.id = t.pid
             )
    SELECT  *
    FROM    cte c

Output:

id  pid
3   1
4   1
5   3
6   5
7   6
8   6

EDIT:

To use in another select statement:

WITH    cte
      AS ( SELECT   *
           FROM     @t
           WHERE    pid = @p
           UNION ALL
           SELECT   t.*
           FROM     @t t
                    JOIN cte c ON c.id = t.pid
         )
SELECT  *
FROM    cte c
JOIN AnotherTable t on c.id = t.id