DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find all the bosses and all the employees?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate how to apply recursive CTE for organizational chart processing.

 

-- SQL recursive query - recursive CTE - find chain of command

-- T-SQL recursive Common Table Expression - tree processing

USE AdventureWorks;

 

DECLARE  @EmployeeID  AS INT;

 

SET @EmployeeID = 100;

 

WITH EmployeeCTE(Name,EmployeeID,ManagerID)

     AS (SELECT LastName + ', ' + FirstName,

                EmployeeID,

                ManagerID

         FROM   HumanResources.Employee AS E

                JOIN Person.Contact AS C

                  ON C.ContactID = E.ContactID

         WHERE  EmployeeID = @EmployeeID

         UNION ALL

         SELECT C.LastName + ', ' + C.FirstName,

                HRE.EmployeeID,

                HRE.ManagerID

         FROM   HumanResources.Employee AS HRE

                JOIN Person.Contact AS C

                  ON C.ContactID = HRE.ContactID

                JOIN EmployeeCTE AS E

                  ON E.ManagerID = HRE.EmployeeID)

SELECT ChainOfCommand=Name

FROM   EmployeeCTE;

GO

/* ChainOfCommand

 

Sacksteder, Lane

Li, Yuhong

Krebs, Peter

Hamilton, James

Sánchez, Ken

*/

------------

-- Microsoft SQL Server T-SQL recursive Common Table Expression - CTE

-- T-SQL organinational chart, orgchart, tree processing

-- MSSQL inner join, left join, self join

-- T-SQL select from select, derived table, subselect, subquery

USE AdventureWorks;

WITH cteEmployeeManager(EmployeeID,Name,ManagerID,LEVEL)

     AS (-- Anchor (root) Member (AM)

         SELECT EmployeeID,

                c.LastName + ', ' + c.FirstName,

                ManagerID,

                0

         FROM   Person.Contact c

                JOIN HumanResources.Employee e

                  ON c.ContactID = e.ContactID

         WHERE  ManagerID IS NULL

         UNION ALL

         -- Recursive Member (RM)

         SELECT e.EmployeeID,

                c.LastName + ', ' + c.FirstName,

                e.ManagerID,

                M.LEVEL + 1

         FROM   Person.Contact c

                INNER JOIN HumanResources.Employee e

                  ON c.ContactID = e.ContactID

                INNER JOIN cteEmployeeManager AS M

                  ON E.ManagerID = M.EmployeeID)

SELECT   CEO = l0.Name,

         Executive = l1.Name,

         Manager = l2.Name,

         Supervisor = ISNULL(l3.Name,''),

         Staff = ISNULL(l4.Name,'')

FROM     (SELECT Name,

                 EmployeeID,

                 ManagerID

          FROM   cteEmployeeManager

          WHERE  LEVEL = 0) l0

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 1) l1

           ON l1.ManagerID = l0.EmployeeID

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 2) l2

           ON l2.ManagerID = l1.EmployeeID

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 3) l3

           ON l3.ManagerID = l2.EmployeeID

         LEFT JOIN (SELECT Name,

                           EmployeeID,

                           ManagerID

                    FROM   cteEmployeeManager

                    WHERE  LEVEL = 4) l4

           ON l4.ManagerID = l3.EmployeeID

ORDER BY l1.Name,

         l2.Name,

         l3.Name,

         l4.Name

 

GO

Related article:

 

Recursive Queries Using Common Table Expressions

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE