Hierarchy Table SQL Statement using CTE (Common Table Expressions)

Assuming a table with fields id, name, parent_id where parent_id is self referencing.

We can use the T-SQL Common Table Expression to easily loop through all children items.

Example Table:
(categories)
id | name | parent_id
1 | CatA | NULL
2 | CatB | 1
3 | CatC | 1
4 | CatD | 2
5 | CatE | 3

 -- This will select all children belonging to CatA where id = 1
;With Hierarchy 
As
(
    SELECT    id, name, parent_id
    FROM      categories
    WHERE     id = 1 -- CatA Id
    UNION  ALL
    SELECT    child.id, child.name, child.parent_id
    FROM      categories child
    INNER JOIN  Hierarchy parent on child.parent_id = parent.id
)
-- This will select all children from the Hierarchy
Select * from Hierarchy