How to Use Recursive Self-Join in SQL Server to Display Hierarchical Data Ancestors?

Tip of using recursive self-join to represent hierarchical data in SQL Server

Suppose you have a table called "Categories" with columns Id, Name, and ParentId, allowing the creation of unlimited hierarchies within categories. To display the Business Laptops category and all its ancestor categories, you can leverage the power of recursive common table expressions (CTEs).

Let’s create a sample “Categories” table and fill it with data to illustrate the solution:

<code class="language-sql">DECLARE @Categories AS TABLE
(
    Id INT,
    Name VARCHAR(100),
    ParentId INT
);

INSERT INTO @Categories VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4);</code>

Next, we create a recursive CTE called "PathFinder" to iterate over parent-child relationships and concatenate ancestor names to the "Path" column:

<code class="language-sql">WITH PathFinder AS
(
    SELECT Id, Name, Name AS Path, ParentId
    FROM @Categories 
    WHERE ParentId IS NULL
    UNION ALL
    SELECT t.Id, t.Name, CAST(cf.Path + ', ' + t.Name AS VARCHAR(100)), t.ParentId
    FROM @Categories t
    INNER JOIN PathFinder cf ON t.ParentId = cf.Id
)</code>

Finally, we can use the PathFinder CTE to retrieve the name of the "Business Laptops" category and its ancestors:

<code class="language-sql">SELECT Name, Path
FROM PathFinder
WHERE Name = 'Business Laptops';</code>

This query will return the category name and its ancestors separated by commas.

Here’s a breakdown of recursive queries:

• Base case: The first part of the CTE selects a category that has no parent (i.e. the root node) and initializes its "Path" with its name.
• Recursive case: The second part recursively joins the categories to their parents and appends their names to the "Path" column. This process will continue until there are no more unprocessed categories.
• Result: CTE generates a sequence of rows, each representing a category and its complete ancestors. By selecting the required category name from this CTE we can retrieve the required data.

Using this recursive approach, you can efficiently represent and query hierarchical data in SQL Server, which provides a valuable technique for managing and analyzing data with inherent relationships.

The above is the detailed content of How to Use Recursive Self-Join in SQL Server to Display Hierarchical Data Ancestors?. For more information, please follow other related articles on the PHP Chinese website!