Recursive CTEs: Opening the door to a new dimension
July 18, 2013 Leave a comment
A few weeks ago, someone asked me what a Common Table Expression (CTE) was, and how it works. But writing about a CTE would result in a short blog post. So why not make it a little more interesting, and also write about recursive CTE’s, right?
Since SQL Server 2005 we have the opportunity to use something called a Common Table Expression, or CTE for short. This T-SQL feature allows us to use the resultset of a query, as a source for another statement, just like a physical table. This sounds weird, but let me show you.
Creating the resources needed
To explain the possibilities of a CTE, let’s create a simple example with employee data. We’re going to create a table that contains the employee’s ID, name, job title and the employee ID of the manager:
CREATE TABLE dbo.Employees (EmployeeID INT, EmployeeName VARCHAR(25), JobTitle VARCHAR(50), ManagerID INT) INSERT INTO dbo.Employees (EmployeeID, JobTitle, EmployeeName, ManagerID) VALUES (1, 'CEO', 'Smith', NULL), (2, 'Department Manager', 'Winters', 1), (3, 'Team Manager', 'Thompson', 2), (4, 'Employee', 'Jones', 3);
If you select all records from this table, it will look like this:
So as you can see, every employee has a manager, except the CEO. He’s at the top of the food chain and no one has a higher position than him. And looking at the “ordinary” employee, you’ll see that he has a manager above him, but no employees beneath him. He’s at the bottom of the food chain so to speak.
But what about the CTE? Let’s write an easy example to show you how to add the employees last names and job titles together, without doing that in your final select statement:
WITH EmployeeList AS ( SELECT EmployeeID, EmployeeName + ' - ' + JobTitle AS EmployeeNameAndJobTitle FROM dbo.Employees ) SELECT EmployeeID, EmployeeNameAndJobTitle FROM EmployeeList;
If we analyse the example above, the select query that adds the employees last name and job title together becomes a new resultset that is called “EmployeeList”, and can be used just like a normal table in the query below the CTE. The result looks like this”
But remember: the CTE just exists in the scope of your query. This means that you can only run one select statement on the CTE. If you try to run a second select statement, you’ll see that the CTE will no longer exist.
Another thing you can do with a CTE, is directly add the column names to the CTE like this:
WITH EmployeeList (EmployeeID, EmployeeNameAndJobTitle) AS ( SELECT EmployeeID, EmployeeName + ' - ' + JobTitle FROM dbo.Employees ) SELECT EmployeeID, EmployeeNameAndJobTitle FROM EmployeeList;
This might be easier to read for you and your colleagues, instead of naming your column in the select with an “AS EmployeeID”. The result is exactly the same as in the example above.
This is just an easy example, but imagine that you can use this to join your aggregated temporary dataset from your CTE, onto another table that you need to build a final resultset.
But now comes the best part: recursive CTE’s. To stick with this example, what if you want to combine the job title of the employee with the manager above him? This sort of resultsets can be created with a recursive CTE. In the example below, the job title of the manager is added to the employees job title:
WITH HierarchyCTE (EmployeeID, ManagerID, JobTitle, Hierarchy) AS (SELECT EmployeeID, ManagerID, JobTitle, CONVERT(VARCHAR(35), '') FROM dbo.Employees WHERE ManagerID IS NULL UNION ALL SELECT E.EmployeeID, E.ManagerID, E.JobTitle, CONVERT(VARCHAR(35), H.JobTitle + ' \ ' + E.JobTitle) FROM dbo.Employees E INNER JOIN HierarchyCTE H ON H.EmployeeID = e.ManagerID ) SELECT EmployeeID, ManagerID, JobTitle, Hierarchy FROM HierarchyCTE;
This is what the result looks like:
What happens in the recursive CTE, is that the first query is used as an anchor, and a loop is executed to add every child-record to the anchor (parent)-record. If there are no child-records left for the parent, the next parent is fetched, and so on.
So what can you do with this?
The examples above were just easy examples, but you can imagine the opportunities with this functionality. For example, if you work for an electronics store, you could build a long string of all packages with sub-deliveries (when every phone is send with a screen protector and case for example). Or create a complete overview of contents for recipes (for example, which ingredients are put into a pie). The possibilities are endless…