What is a Common Table Expression (CTE)

A CTE can be thought of as a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table while providing the same benefits to the user. However; a CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.

Building a Recursive CTE

In the following examples, you will learn how to harness the power of a recursive CTE query by fulfilling a common business requirement, retrieving hierarchical data. By the time the final query is complete you will be able to easily determine how many levels from the top executive each employee is.

A recursive CTE requires four elements in order to work properly.

Anchor query (runs once and the results ‘seed’ the Recursive query)

Recursive query (runs multiple times and is the criteria for the remaining results)

UNION ALL statement to bind the Anchor and Recursive queries together.

INNER JOIN statement to bind the Recursive query to the results of the CTE.

Identify the Anchor and Recursive Query

Anyone who does not have a boss is considered to be at the top level of the company and everyone who does have a boss either works for the person(s) at the top level (upper management), or the people that work for them (mid-management thru base employees).

For example, a CEO is at the top level and thus has a ManagerID of null. Likewise, everyone below the CEO will have a ManagerID. This is demonstrated in the two queries below:

The first SELECT statement will become your Anchor query as it will find the employee that has a ManagerID of null (representing Level 1 of the organization). The second SELECT statement will become your Recursive query and it will find all employees that do have a ManagerID (representing Level 2-3 of this organization).

As you can see from the results so far, these queries are unable to give hierarchical data on which level each employee is at within the organization.

Add the Anchor and Recursive query to a CTE

Begin transforming this entire query into a CTE by placing a UNION ALL statement between the Anchor and Recursive queries. Now add parentheses around the entire query, indenting it, moving it down, and adding the declaration WITH EmployeeList AS before the open parenthesis, and then add SELECT * FROM EmployeeList on the next line after the close parenthesis.

Your query should now look like the screenshot below:

As you can see, the results from your CTE are exactly the same as the results returned from running the anchor and Recursive queries simultaneously in the previous example.

Add an expression to track hierarchical level

The Anchor query (aliased as ‘Boss’) inside the CTE represents everyone at Level 1 (i.e. Sally Smith). The Recursive query (aliased as ‘Emp’) represents everyone at Levels 2 and 3. In order to visualize each level in a result set, you will need to add an expression field to each query.

Add the expression “1 AS EmpLevel” to the Anchor query and the expression “2 AS EmpLevel” to the Recursive query. Before executing the entire query, look closely at the expression field. The EmpLevel expressions in the Anchor query will hard-code the numeral 1 (for Sally Smith’s level), while the EmpLevel expressions in the Recursive query will hard-code the numeral 2 for everyone else.

Your query should now look like the screenshot below:

The two new expression fields were a helpful step. In fact, they show the correct EmpLevel information for Sally Smith and for the people at Level 2 (i.e., Adams, Bender, Brown, Kennson, Lonning and Osako). However, the 2 is just a hard-coded placeholder to help visualize your next step. Lisa Kendall and several other employees need to be at Level 3.

Ideally you would like to make the expression dynamic by replacing “2 AS EmpLevel” with the expression “EmpLevel + 1”.

Add a self-referencing INNER JOIN statement

Let’s take a moment and recognise why this is not going to work quite so simply. The idea to increment EmpLevel in the recursive query of the CTE is on the right track. Unfortunately, the recursive query is trying to reference a field called EmpLevel but can’t find one, since it has only been materialized in the result set of the Anchor query and does not yet exist in the recursive set.

How can you materialize the EmpLevel field for the recursive query? We can use the CTE for this! Remember, a recursive CTE requires an INNER JOIN to connect the recursive query to the CTE itself. Go ahead and write an INNER JOIN statement binding the recursive query ‘Emp’ to the CTE ‘EmployeeList AS EL’ ON Emp.ManagerID = EL.EmpID.

Your query should now look like the screenshot below:

Success! You can now see that Sally is at the first level, Alex is at the second level and Lisa appears at the third level. Since a CTE can reference itself, the ‘Emp’ recursive query can now access the EmpLevel field materialized in the EmployeeList CTE.

What if I have periods in respect i.e what if I have a manager of that period and next period I have another managers , and when to get the manager of managers I have to take periods in consideration .?

The problem is that when the list reaches no. 15 , it starts taking a lot of resources and the query takes 1.12 minutes to load the results which otherwise takes only a second. Please give me a solution for this as I have failed to find a solution fot this issue.

——————————————————————————-

The query that I am using is as following:
——————————————————————————-

I need one more thing. Essentially an indented BOM. How can I take the employee list above and sort it so that Each employee comes out immediately under their Boss??
11 Sally
1 Alex
2 Barry
3 Lee
9 James
10 Terry
4 David
6 Lisa
8 John
12 Barbara
12 Phil

Great example, thank you.
Here’s a query using the above concept to get an account’s hoerarchy (parent accounts above and child accounts below) from Dynamics CRM 2011. You need the recursive CTE twice because it doesn’t survive after the initial select. Wrap up in a stored procedure with the accountid as a parameter for ease of use :):

For me have been really dificult to understand how recursive CTE works. Now I have several tables each one with a hierachyid structure and datatypes. For example, I have a Table of Companies because one company may be on charge to others companies (or subsidiaries). I have another table of areas or departaments this one has a hierarchyid structure too, each area or departament has a foreign key referencing the company (or subsidiary) to wich it belongs. I have another table of positions with a hierarchyid structure too, each position has a foreign key referencing the area or departament to wich it belongs and finally I have another table of employees with a hierachyid structure too, each employee has a foreigh key referencing the position to wich he belongs. How to build a query to draw all organization chart going over all tables in hierarchyid structure? I thing i have to use a CTE to go for eache node and its children from zero node to each node with no children. Is ther another easier way to do it keepping hierarcyid datatype an structure in SQL Server? Please help me how.

Dear Pinal:
For every google search I do when I have a SQL problem, if I see your name in the results I always go there first. This post in particular was a great help when I needed it the most. Thank you very much for your great examples and clear explanations! The world is a better place because of you!

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.