T-SQL How to apply recursive queries for organizational charts?

Execute the following Microsoft SQL Server T-SQL recursive scripts in Management Studio Query Editor to get the "immediate supervisor" organizational chart of AdventureWorks Cycles and the number of subordinates at each level.

-- SQL recursive Common Table Expression - CTE - tree processing, tree parsing

-- MSSQL organizational chart - orgchart - direct report - chain of command

USE AdventureWorks;

GO

WITH cteSupervisor(ManagerID,EmployeeID,EmployeeLevel)

     AS (SELECT ManagerID,

                EmployeeID,

                EmployeeLevel = 0

         FROM   HumanResources.Employee

         WHERE  ManagerID IS NULL

         UNION ALL

         SELECT e.ManagerID,

                e.EmployeeID,

                EmployeeLevel + 1

         FROM   HumanResources.Employee e

                INNER JOIN cteSupervisor d

                  ON e.ManagerID = d.EmployeeID)

SELECT Manager = CO.LastName + ', ' + CO.FirstName,

       Employee = C.LastName + ', ' + C.FirstName,

       EmployeeLevel

FROM   cteSupervisor DR

      INNER JOIN HumanResources.Employee E

         ON DR.EmployeeID = E.EmployeeID

      INNER JOIN Person.Contact C

         ON E.ContactID = C.ContactID

      LEFT JOIN HumanResources.Employee EM

         ON DR.ManagerID = EM.EmployeeID

      LEFT JOIN Person.Contact CO

         ON EM.ContactID = CO.ContactID

GO

-- Results

Manager	Employee	EmployeeLevel
NULL	Sánchez, Ken	0
Sánchez, Ken	Bradley, David	1
Sánchez, Ken	Duffy, Terri	1
Sánchez, Ken	Trenary, Jean	1
Sánchez, Ken	Norman, Laura	1
Sánchez, Ken	Hamilton, James	1
Sánchez, Ken	Welcker, Brian	1
Welcker, Brian	Jiang, Stephen	2
Welcker, Brian	Alberts, Amy	2
Welcker, Brian	Abbas, Syed	2
Abbas, Syed	Tsoflias, Lynn	3
Alberts, Amy	Pak, Jae	3
Alberts, Amy	Varkey Chudukatil, Ranjit	3
Alberts, Amy	Valdez, Rachel	3

....

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

-- T-SQL count descendants at each level - count subordinates in organization

-- Parent-child hierarchy - count children by level

DECLARE  @EmployeeID INT,  @Supervisor NVARCHAR(50)

DECLARE curEmployee CURSOR  FOR

SELECT EmployeeID, FullName = FirstName + ' ' + LastName

FROM   AdventureWorks.HumanResources.Employee e

       INNER JOIN AdventureWorks.Person.Contact pc

         ON pc.ContactID = e.ContactID

DECLARE  @Descendant  TABLE(

                            EmployeeID        INT,

                            FullName          NVARCHAR(50),

                            [Level]           INT    NULL,

                            [DescendantCount] INT    NULL

                            )

OPEN curEmployee

FETCH NEXT FROM curEmployee

INTO @EmployeeID, @Supervisor

WHILE (@@FETCH_STATUS = 0 )

  BEGIN

    WITH cteSubTree

         AS (SELECT EmployeeID, 0 AS [Level]

             FROM   AdventureWorks.HumanResources.Employee e

             WHERE  EmployeeID = @EmployeeID -- root

             UNION ALL

             SELECT e.EmployeeID, [Level] + 1 -- recursive term

             FROM   cteSubTree c

                    INNER JOIN AdventureWorks.HumanResources.Employee e

                      ON c.EmployeeID = e.ManagerID)

    INSERT INTO @Descendant

    SELECT   @EmployeeID,

             @Supervisor,

             [Level],

             COUNT(* )  

    FROM     cteSubTree

    GROUP BY [Level]

    FETCH NEXT FROM curEmployee

    INTO @EmployeeID,  @Supervisor

  END

SELECT   FullName,

         [Level],

         DescendantCount,

         t.EmployeeID

FROM     @Descendant t

         INNER JOIN (SELECT   EmployeeID

                     FROM     @Descendant

                     GROUP BY EmployeeID

                     HAVING   count(* ) > 1) g

           ON t.EmployeeID = g.EmployeeID

ORDER BY t.EmployeeID, [Level]

CLOSE curEmployee

DEALLOCATE curEmployee

GO

/*

FullName                Level DescendantCount   EmployeeID

Roberto Tamburello      0     1                 3

Roberto Tamburello      1     7                 3

Roberto Tamburello      2     5                 3

David Bradley           0     1                 6

David Bradley           1     8                 6

JoLynn Dobney           0     1                 7

JoLynn Dobney           1     6                 7

....

*/

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