SQL Bribery: 2021

CTE in SQL

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data as the CTE continues to execute until the query returns the entire hierarchy.

A typical example of hierarchical data is a table that includes a list of employees. For every employee, the table provides a reference to that person’s manager. That reference is itself an employee ID within the same table. You can use a recursive CTE to display the hierarchy of employee data.

Example 1

CREATE TABLE Employees
(
  EmployeeID int NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  ManagerID int NULL
)

INSERT INTO Employees VALUES (1, 'Kent', 'Thompson', NULL)
INSERT INTO Employees VALUES (2, 'Tim', 'Ryan', 1)
INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1)
INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2)
INSERT INTO Employees VALUES (5, 'Kenny', 'Erickson', 2)
INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3)
INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3)
INSERT INTO Employees VALUES (8, 'Danial', 'Mark', 5)
INSERT INTO Employees VALUES (9, 'David', 'Matthew', 6)
INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM Employees e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT
  FirstName + ' ' + LastName AS FullName, MgrID,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

Example 2

CREATE TABLE Employees1
(
  EmployeeID int NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  ManagerID int NULL
)

INSERT INTO Employees1 VALUES (1, 'Kent', 'Thompson', NULL)
INSERT INTO Employees1 VALUES (2, 'Tim', 'Ryan', 1)
INSERT INTO Employees1 VALUES (3, 'Robert', 'Durello', 1)
INSERT INTO Employees1 VALUES (4, 'Rob', 'Bailey', 2)
INSERT INTO Employees1 VALUES (5, 'Kenny', 'Erickson', 2)
INSERT INTO Employees1 VALUES (6, 'Bill', 'Goldberg', 3)
INSERT INTO Employees1 VALUES (7, 'Ryan', 'Miller', NULL)
INSERT INTO Employees1 VALUES (8, 'Danial', 'Mark', 5)
INSERT INTO Employees1 VALUES (9, 'David', 'Matthew', 6)
INSERT INTO Employees1 VALUES (10, 'Michael', 'Jhonson', 6)
INSERT INTO Employees1 VALUES (11, 'Eric', 'Federick', 7)
INSERT INTO Employees1 VALUES (12, 'Celine', 'George', 7)
INSERT INTO Employees1 VALUES (13, 'Zeith', 'Marcus', 12)

WITH
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees1
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
      r.EmpLevel + 1
    FROM Employees1 e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID
  )
SELECT EmpID,
  FirstName + ' ' + LastName AS FullName, MgrID,
  EmpLevel,
  (SELECT FirstName + ' ' + LastName FROM Employees1
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID

Example 3

CREATE TABLE Employees2
(
EmployeeID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Department varchar(50) NOT NULL,
ManagerID int NULL
)

INSERT INTO Employees2 VALUES (1, 'Kent', 'Thompson', 'Finance', NULL)
INSERT INTO Employees2 VALUES (2, 'Tim', 'Ryan','Finance', 1)
INSERT INTO Employees2 VALUES (3, 'Robert', 'Durello','Finance', 1)
INSERT INTO Employees2 VALUES (4, 'Rob', 'Bailey','Finance', 2)
INSERT INTO Employees2 VALUES (5, 'Kenny', 'Erickson','Finance', 2)
INSERT INTO Employees2 VALUES (6, 'Bill', 'Goldberg','Finance', 3)
INSERT INTO Employees2 VALUES (7, 'Ryan', 'Miller','Marketing', NULL)
INSERT INTO Employees2 VALUES (8, 'Danial', 'Mark','Finance', 5)
INSERT INTO Employees2 VALUES (9, 'David', 'Matthew','Finance', 6)
INSERT INTO Employees2 VALUES (10, 'Michael', 'Jhonson','Finance', 6)
INSERT INTO Employees2 VALUES (11, 'Eric', 'Federick', 'Marketing',7)
INSERT INTO Employees2 VALUES (12, 'Celine', 'George','Marketing', 7)
INSERT INTO Employees2 VALUES (13, 'Zeith', 'Marcus', 'Marketing',12)

WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel, Department)
AS
(
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1, Department
    FROM Employees2
    WHERE ManagerID IS NULL

    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1 , e.Department
    FROM Employees2 e
      INNER JOIN cteReports r
        ON e.ManagerID = r.EmpID

)
SELECT EmpID,
FirstName + ' ' + LastName AS FullName, Department,
EmpLevel, MgrID,
(SELECT FirstName + ' ' + LastName FROM Employees2
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY Department, EmpLevel, MgrID

SQL Bribery

Sunday, November 21, 2021

CTE in SQL (To Display The Hierarchy of Employee Data)

How to use @@ROWCOUNT in SQL Server