Creating a recursive CTE

A recursive CTE is one where the query references itself. Think of a list of employees and the person who manages them, a parent/child relationship, a bill of materials, or other organizational/hierarchical situations. These are the types of relationships you can express using a recursive CTE.

In this section, we will examine how to take that relationship and show all the levels of a hierarchy using a CTE. We will first need to create some data to use, and then we will create a query that shows all the levels of an organizational hierarchy.

Creating the hierarchical data

The WorldWideImporters database doesn’t have any tables with a parent/child relationship, so we are going to create a simple Employee table and populate it with some data to use:

-- Create an Employee table.
CREATE TABLE dbo.Employee
(
EmpID SMALLINT NOT NULL,
FirstNm NVARCHAR(30) NOT NULL,
LastNm NVARCHAR(40) NOT NULL,
JobTitle NVARCHAR(50) NOT NULL,
ManagerID SMALLINT NULL...