Common Table Expression (CTE) for Recursive Data

First of all let us create a table called Employee table using the below script.

INSERT INTO Employee VALUES (5,'B_B',2)

INSERT INTO Employee VALUES (9,'BB_B',5)

INSERT INTO Employee VALUES (11,'BC_B',6)

INSERT INTO Employee VALUES (13,'BBB_B',9)

   --Select the root or parent records
    

    SELECT 

    FROM Employee

    WHERE Manager_ID IS NULL

    
    UNION ALL
   

   

 SELECT 

 FROM Employee e

        INNER JOIN DirectReports d

        ON e.Manager_ID = d.Employee_ID

        INNER  JOIN employee m ON e.manager_ID = m.employee_id

)

   (Manager_ID INT,
   

   ManagerName VARCHAR(100),
   

   Employee_ID INT,
   

   EmployeeName VARCHAR(100) ,
   

   EmployeeLevel INT)--Selecting the 0th level (who do not have manager) employee

    SELECT Manager_ID,
    

    CAST('' AS VARCHAR(100))AS ManagerName,
    

    Employee_ID, EmployeeName ,
    

    0 AS EmployeeLevel

    FROM Employee

    WHERE Manager_ID IS NULL 

WHILE(@@ROWCOUNT>0)BEGIN
    
       SET 

     

       SET @Recursion =@Recursion +1

       INSERT INTO @DirectReports
       

       SELECT
       

       e.Manager_ID,
       

       m.EmployeeName AS ManagerName,
       

       e.Employee_ID,
       

       e.EmployeeName ,
       

       @Recursion
       

       FROM Employee e

        INNER JOIN @DirectReports d

        ON e.Manager_ID = d.Employee_ID

        INNER  JOIN employee m ON e.manager_ID = m.employee_id

        WHERE d.EmployeeLevel=@Recursion -1 --to select only the last execution result
   

   END
SELECT 

SELECT * FROM @DirectReports   

CREATE TABLE Employee

(Employee_id             INT PRIMARY KEY,

EmployeeName          VARCHAR(100),

Manager_id                 INT

)

Let us populate the sample data.

INSERT INTO Employee VALUES (1,'A',NULL)

INSERT INTO Employee VALUES (2,'A_B',1)

INSERT INTO Employee VALUES (3,'A_C',1)

INSERT INTO Employee VALUES (4,'A_D',1)

INSERT INTO Employee VALUES (6,'B_C',2)

INSERT INTO Employee VALUES (7,'C_B',3)

INSERT INTO Employee VALUES (8,'C_C',3)

INSERT INTO Employee  VALUES (10,'BB_C',5)

INSERT INTO Employee VALUES (12,'BC_C',6)

INSERT INTO Employee VALUES (14,'BBC_B',10)  

I have used separate insert statement for better readability.

Here Manager_id is a foreign key referring to Employee_id.

Let us assume that we need to generate an employee reports with Employee_id,Employee Name ,Manager_id,Manager Name and hierarchical position (level) of employee in the organization. This can be implemented very easily using recursive CTE which introduced in SQL server 2005.Below script will give the result.

;WITH DirectReports(Manager_ID, ManagerName,Employee_ID, EmployeeName ,EmployeeLevel)

AS (

         Manager_ID,

         CAST('' AS VARCHAR(100))AS ManagerName, 

         Employee_ID, 

         EmployeeName,

         0 AS EmployeeLevel

 --Recursive part :Select the child

     e.Manager_ID,

     m.EmployeeName AS ManagerName, 

     e.Employee_ID, 

     e.EmployeeName,

     EmployeeLevel + 1

SELECT * FROM DirectReports ;

Let us see how we can write the same with out CTE, which will help us to visualize the recursive CTE.

DECLARE @DirectReports AS TABLE

INSERT INTO @DirectReports

DECLARE @Recursion INT =0