How Can Recursive CTEs in SQL Server Retrieve a Category and its Ancestor Path?

Self-referential query using recursive common table expressions (CTE) in SQL Server

Introduction

In relational databases, self-referential queries are a powerful technique for traversing hierarchical relationships in a single table. SQL Server provides common table expressions (CTEs) for this purpose, enabling efficient and expressive recursive queries.

Question

Consider a simplified "Category" table with ID, Name and ParentId columns. The table supports an indeterminate hierarchy of categories, as shown in the following example:

<code>Id     Name       ParentId
1      Business   NULL
2      Laptops    1
3      Asus       2</code>

The goal is to construct a query that not only retrieves a specified category (e.g., "Business Laptops") but also provides a comma-separated list of all its parent categories.

Solution: Recursive CTE

To solve this problem, recursive CTE can be used. The outermost CTE defines the starting point of the recursion, while the recursive term defines the logic of the extended hierarchy:

<code>WITH CTE AS (
    SELECT Id, Name, Name AS Path, ParentId
    FROM Categories 
    WHERE ParentId IS NULL
    UNION ALL
    SELECT t.Id, t.Name, cast(cte.Path + ',' + t.Name as varchar(100)), t.ParentId
    FROM Categories t
    INNER JOIN CTE ON t.ParentId = CTE.Id
)</code>

Query

To retrieve a specified category and its parent category, we use a simple query:

<code>SELECT Id, Name, Path
FROM CTE
WHERE Name = 'Business Laptops'</code>

Results

The query returns the following results:

<code>Id     Name       Path
2      Laptops    Business,Laptops</code>

In this example, "Business Laptops" is a child of "Laptops" which is a child of "Business". The Path column provides the complete hierarchical path back to the root category.

Conclusion

Recursive CTEs in SQL Server provide an elegant and efficient way to navigate hierarchical relationships in tables. By leveraging the power of recursion, complex queries can be constructed to extract valuable insights from hierarchical data structures.

The above is the detailed content of How Can Recursive CTEs in SQL Server Retrieve a Category and its Ancestor Path?. For more information, please follow other related articles on the PHP Chinese website!