Home > Common Table Expressions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Common Table Expressions (CTE) – Part 1 – Recursive Query

TSQL – Common Table Expressions (CTE) – Part 1 – Recursive Query

Let’s take a look at how CTE can be used to create a recursive query:

In a typical employee – supervisor scenario, A supervisor is an employee and an employee can be a supervisor of another employee(s).

This is also known as a self-referencing. For example:

image

The above table contains a list of Employee IDs and Employee Names. It also contains a column named ManagerID for Employee’s Manager. The Manager here is also an employee.

To generate a hierarchical list of all employees:
1.
we first need to retrieve root employee, i.e. top level employee,
2.
after the root level is retrieved, we need to find all child nodes for that root level,
3.
perform step 2 until all levels are retrieved.

This can be achieved using a Recursive Common Table Expression, which consists of two parts:
1. Retrieve root level – this is known as Anchor in a Recursive CTE
2.
retrieve all children of root level – this is known as Recursive Part in a Recursive CTE.

These process is performed recursively until all level are retrieved.

The Anchor part will be written as follows: (Retrieving root level)

SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, 0 AS EmpLevel
FROM dbo.Employees Emp
WHERE ManagerID IS NULL

And, the recursive part is written as: (Retrieving all children of root), “EmployeeHierarchy” will be the CTE itself, hence making it recursive:

SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, EmpLevel + 1
FROM dbo.[Employees] Emp
INNER JOIN EmployeeHierarchy AS EmpH ON Emp.ManagerID = EmpH.EmpID

After combining both parts the full query will be:

WITH EmployeeHierarchy(ManagerID, EmpID, EmpName, EmpLevel)
AS
( 
      SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, 0 AS EmpLevel
      FROM dbo.Employees Emp
      WHERE ManagerID IS NULL 
 
      UNION ALL 
 
      SELECT Emp.ManagerID, Emp.EmpID, Emp.EmpName, EmpLevel + 1
      FROM dbo.Employees Emp
      INNER JOIN EmployeeHierarchy AS EmpH ON Emp.ManagerID = EmpH.EmpID
)     SELECT ManagerID, EmpID, EmpName, EmpLevel
      FROM EmployeeHierarchy
      ORDER BY ManagerID

The output of above query:

image

Download the sample scripts used in this example here.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. geekzrool
    October 13, 2012 at 12:42 am

    Msg 530, Level 16, State 1, Line 1
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    i mean would this really actually work on a real table with millions of rows? not…

  2. Cdrackgt
    November 29, 2012 at 11:11 pm

    It does.. you just have to set the OPTION (MAXRECURSION XXX) value on the last query

    like this:

    SELECT ManagerID, EmpID, EmpName, EmpLevel
    FROM EmployeeHierarchy
    ORDER BY ManagerID
    OPTION (MAXRECURSION 5000)

    check at http://sqlwithmanoj.wordpress.com/2011/12/23/recursive-cte-maximum-recursion-100-has-been-exhausted/

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: