How Can I Perform Recursive Self-Joins in SQL Server to Traverse Hierarchical Data?

SQL Server recursive self-join: an easy method

In database management, recursive self-join is a technology that allows tables to self-join multiple times, capable of traversing hierarchical data. In SQL Server, this can be achieved through recursive queries.

Consider the following scenario: We have a table called "Person" with the following columns:

<code>PersonID | Initials | ParentID</code>

The table represents a hierarchy where each person has an "Initials" column and may have a "ParentID" referring to another person.

To perform a recursive self-join on the "Person" table, we can use a common table expression (CTE) as shown below:

<code class="language-sql">WITH q AS 
(
    SELECT  *
    FROM    Person
    WHERE   ParentID IS NULL 
    UNION ALL
    SELECT  m.*
    FROM    Person m
    JOIN    q
    ON      m.parentID = q.PersonID
)
SELECT  *
FROM    q</code>

In this CTE:

1. The initial query selects all rows in the "Person" table whose "ParentID" is NULL, representing the ultimate ancestor.
2. The recursive part of the CTE selects rows in the "Person" table whose "ParentID" matches the "PersonID" in the "q" CTE. This step effectively extends the hierarchy.
3. The final query selects all rows in the "q" CTE, providing hierarchical data.

In order to maintain the order of the tree, you can add sorting conditions in the recursive query, as follows:

<code class="language-sql">WITH q AS 
(
    SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
    FROM    Person m
    WHERE   ParentID IS NULL
    UNION ALL
    SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
    FROM    Person m
    JOIN    q
    ON      m.parentID = q.PersonID
)
SELECT  *
FROM    q
ORDER BY
        bc</code>

You can control the ordering of sibling elements in the hierarchy by changing the ORDER BY condition.

The above is the detailed content of How Can I Perform Recursive Self-Joins in SQL Server to Traverse Hierarchical Data?. For more information, please follow other related articles on the PHP Chinese website!