Traversing hierarchies with nodes having multiple parents

Given a table called Relations with columns IdNode, IdParentNode that describes relations between nodes, the following query uses a recursive CTE to return all ancestors (at all levels) for all child nodes.

;WITH cteTree AS
(
    SELECT  IdNode, IdParentNode, 1 as TreeLevel
    FROM Relations

    UNION ALL

    SELECT  cteTree.IdNode, R.IdParentNode, TreeLevel + 1
    FROM
                cteTree
                INNER JOIN Relations R ON cteTree.IdParentNode = R.IdNode
)
SELECT IdNode, IdParentNode, TreeLevel
FROM cteTree
OPTION(MAXRECURSION 10)

The OPTION(MAXRECURSION 200) means that it looks only 10 levels deep. You can use OPTION(MAXRECURSION 0) if you want to traverse all the levels, though you have to make sure you don't have a circular reference.

Is this useful?