SQL Server: recursion in queries

Some data is hierarchical or recursive in nature. Such data can be stored in a table by having it point back to itself.

To query this table recursively we can use the WITH clause to create a Common Table Expression. A CTE can be useful instead of temporary tables or sub queries. But here we use it for its desirable property of being able to reference itself.

WITH Branches (Id, Name)
AS
(
    SELECT t.Id, CAST(t.Name AS VARCHAR(512))
    FROM Tree t
    WHERE t.ParentId IS NULL
 
    UNION ALL
 
    SELECT t.Id, CAST(b.Name + t.Name AS VARCHAR(512)) AS Name
    FROM Tree t INNER JOIN Branches b ON t.ParentId = b.Id
)
SELECT Name FROM Branches ORDER BY Name

A recursive WITH query consists of three parts. First we select all the root elements. In this case there’s just one but it could be many more.

Secondly we select all the children. This is where the magic happens because the second SELECT will be called recursively until there are no more children. By the way, the CASTs are necessary when concatenating strings.

Finally we use the result in an ordinary SELECT query.