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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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