Archive

Posts Tagged ‘Reference’

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

13.04.2011 2 comments

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

TSQL – Common Table Expressions (CTE) – Part 0 – Introduction

What is it? A CTE Creates a temporary named result set. It is available within the execution of a single DML statement. A Common Table Expression can be used to:

1. Create Recursive Query
2. Create a temporary view, to simplify another query
3. To avoid “Msg 164, Level 15, State 1” – group by a derived column / non deterministic
   
function
4. And, Use the result set returned multiple time in the same statement.

Let’s see how to create one,
I have created two tables for this example – FirstNames, LastNames as below:

Data in FirstNames and LastNames :
image image

To combine the firstname and lastname in output, we can use INNER JOIN:

SELECT FN.EmpID, FN.FirstName, LN.LastName
FROM dbo.FirstNames FN
INNER JOIN dbo.LastNames LN ON LN.EmpID = FN.EmpID

To write the same using CTE, let’s look at the syntax first:

WITH TempResultSetName(Column1, Column2, …)
AS
( 
      SELECT Query
)
SELECT Column1, Column2, FROM TempResultSetName

The “WITH TempResultSetName” defines a name for the result set, and the Column List defines the column names for the result set – this is optional, if column names are not specified it will used the names from the “SELECT Query”.

The “SELECT Query” is the actual data for the results. i.e. which returns actual rows.

The Final part of CTE is the “SELECT Column1, ….. FROM TempResultSetName” – this simply outputs the result set to user. you are not limited to only SELECT here, any valid DML can be used.

However, you can only use the TempResultSet in FROM clause of a SELECT statement, i.e. you can not INSERT INTO or UPDATE or DELETE to/from TempResultSet.

Now, let’s write the actual CTE to replace INNER JOIN:

WITH TempResultSetName(EmpID, FirstName, LastName)
AS
( 
      SELECT FN.EmpID, FN.FirstName, LN.LastName
      FROM dbo.FirstNames FN
      INNER JOIN dbo.LastNames LN ON LN.EmpID = FN.EmpID
)     SELECT EmpID, FirstName, LastName
      FROM TempResultSetName

Line 1 – Defines the Result Set and Columns in Result Set
Line 4 to 6 – Populates the Result Set
Line 8 & 9 – Outputs the Result Set – The output can also include other tables, I have added examples in the sample script.

Download the scripts used in post here.

Hope This Helps!

Vishal