Write a SQL query that flattens a category hierarchy into one row per category with depth and full path.

Electronics is a root, Laptops is its child, and Business is below Laptops. Their depths are 0, 1, and 2, and the Business path includes all three names.

• Assume names fit within the chosen path length.
• Use a recursion limit appropriate for the product's maximum hierarchy depth.
• Treat cycles and orphaned nodes as data-integrity problems.
• Return roots even when they have no children.

The anchor member selects roots and initializes depth and path. The recursive member joins children to the previous level and extends both values. I cast the anchor path to a stable type because recursive CTE columns must have compatible types. A self-referencing foreign key protects parent existence, while cycle prevention needs an additional write rule.

WITH CategoryTree AS
(
    SELECT
        c.CategoryId,
        c.ParentCategoryId,
        0 AS Depth,
        CONVERT(nvarchar(2000), c.Name) AS CategoryPath
    FROM dbo.Categories AS c
    WHERE c.ParentCategoryId IS NULL

    UNION ALL

    SELECT
        child.CategoryId,
        child.ParentCategoryId,
        parent.Depth + 1,
        CONVERT(
            nvarchar(2000),
            parent.CategoryPath + N' > ' + child.Name
        ) AS CategoryPath
    FROM dbo.Categories AS child
    INNER JOIN CategoryTree AS parent
        ON parent.CategoryId = child.ParentCategoryId
)
SELECT CategoryId, ParentCategoryId, Depth, CategoryPath
FROM CategoryTree
ORDER BY CategoryPath
OPTION (MAXRECURSION 100);

An index on ParentCategoryId supports finding children at each level. Recursive traversal is appropriate for moderate trees and occasional reads; heavily queried navigation may justify a closure table, hierarchy path, or cached read model.

• An empty table.
• Several independent roots.
• A root with no children.
• A hierarchy deeper than the configured recursion limit.

• Use multiple roots and uneven depths.
• Include duplicate names under different parents.
• Test a deep chain near the supported limit.
• Verify orphan detection separately because orphans are unreachable from the anchor.

• Starting only from one hard-coded root when all roots are required.
• Using incompatible path data types between anchor and recursive members.
• Assuming a foreign key alone prevents cycles.
• Choosing recursive reads for a hot path without considering a read model.