forum

Home / DeveloperSection / Forums / Help with Writing a Recursive Query in SQL Server

Ask Question
Anubhav Kumar

Anubhav Kumar

Follow
The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Can you answer this question?

Answer

1 Answers

Ravi Vishwakarma

Ravi Vishwakarma

16-Jul-2024

Recursive queries in SQL Server are typically written using Common Table Expressions (CTEs). These are particularly useful for hierarchical data, such as organizational charts, family trees, or any dataset that has a parent-child relationship.

Here's a basic example to illustrate how to write a recursive query using a CTE in SQL Server. We'll use a hypothetical organizational structure as an example.

Example: Organizational Hierarchy

Assume we have a table named Employees with the following structure:

EmployeeID EmployeeName ManagerID
1 Alice NULL
2 Bob 1
3 Carol 1
4 Dave 2
5 Eve 2

In this table:

  • EmployeeID is the unique identifier for each employee.
  • EmployeeName is the name of the employee.
  • ManagerID is the EmployeeID of the employee's manager. The top-level manager (Alice) has a NULL ManagerID.

Recursive Query

Retrieve the entire hierarchy starting from the top-level manager.

Here's how you can write a recursive CTE to achieve this:

WITH EmployeeCTE AS (
    -- Anchor member: this query defines the top-level manager(s)
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level
    FROM 
        Employees
    WHERE 
        ManagerID IS NULL

    UNION ALL

    -- Recursive member: this query joins the CTE with the Employees table
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        c.Level + 1
    FROM 
        Employees e
    INNER JOIN 
        EmployeeCTE c ON e.ManagerID = c.EmployeeID
)
-- Select from the CTE
SELECT 
    EmployeeID,
    EmployeeName,
    ManagerID,
    Level
FROM 
    EmployeeCTE
ORDER BY 
    Level, EmployeeID;

Explanation:

Anchor Member:

  • The first SELECT statement in the CTE defines the anchor member, which retrieves the top-level manager(s) (those with NULL ManagerID).

Recursive Member:

  • The second SELECT statement in the CTE joins the Employees table with the EmployeeCTE on ManagerID and EmployeeID, effectively retrieving the employees managed by each employee in the EmployeeCTE.
  • Level is incremented by 1 to indicate the hierarchy level.

Final SELECT:

  • The final SELECT statement retrieves the data from the CTE, and the ORDER BY clause is used to sort the results by hierarchy level and EmployeeID.

This recursive query will produce the hierarchical structure of the organization, starting from the top-level manager down to the lowest level.

Read more

Define the PIVOT Table with examples in the SQL server.

Explain the SQL triggers and their uses

Explain the SQL Server backups and their types

Liked By