---
title: "Help with Writing a Recursive Query in SQL Server"  
description: "Help with Writing a Recursive Query in SQL Server"  
author: "Anubhav Sharma"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160921/help-with-writing-a-recursive-query-in-sql-server  
category: "SQL Server"  
tags: ["database", "sql server", "sql server 2008", "sql server 2012", "sql server 2022"]  
reading_time: 3 minutes  

---

# Help with Writing a Recursive Query in SQL Server

Hi,

I have a table `EmployeeHierarchy` with `EmployeeID` and `ManagerID`. I need to write a [recursive query](https://www.mindstick.com/forum/161549/how-do-you-write-a-recursive-query-to-fetch-all-descendants-in-a-self-referencing-table) to get the hierarchy of employees. Any [suggestions](https://www.mindstick.com/blog/299486/how-to-write-for-your-audience)?

## Replies

### Reply by Ravi Vishwakarma

Recursive queries in SQL Server are typically written using [**Common Table Expressions (CTEs)**](https://www.mindstick.com/blog/304466/how-do-i-use-cte-to-simplify-complex-queries-in-sql-server). 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](https://www.mindstick.com/blog/202/sub-query-in-sqlserver) 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:

```plaintext
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.**](https://www.mindstick.com/articles/336334/define-the-pivot-table-with-examples-in-sql-server)

[**Explain the SQL triggers and their uses**](https://www.mindstick.com/articles/336344/explain-the-sql-triggers-and-their-uses)

[**Explain the SQL Server backups and their types**](https://www.mindstick.com/articles/336326/explain-the-sql-server-backups-and-their-types)


---

Original Source: https://www.mindstick.com/forum/160921/help-with-writing-a-recursive-query-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.