Defining a recursive common table expression(CTE) : How to show the hierarchical level of managers and the employees who report to them

How to show the hierarchical level of managers and the employees who report to them:

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees

(

      EmployeeID smallint NOT NULL,

      FirstName nvarchar(30)  NOT NULL,

      LastName  nvarchar(40) NOT NULL,

      Title nvarchar(50) NOT NULL,

      DeptID smallint NOT NULL,

      ManagerID int NULL,

 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)

);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES

 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)

,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)

,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)

,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)

,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)

,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

--Records in MyEmployees table

EmployeeID	FirstName	LastName	Title	DeptID	ManagerID
1	Ken	Sánchez	Chief Executive Officer	16	NULL
16	David	Bradley	Marketing Manager	4	273
23	Mary	Gibson	Marketing Specialist	4	16
273	Brian	Welcker	Vice President of Sales	3	1
274	Stephen	Jiang	North American Sales Manager	3	273
275	Michael	Blythe	Sales Representative	3	274
276	Linda	Mitchell	Sales Representative	3	274
285	Syed	Abbas	Pacific Sales Manager	3	273
286	Lynn	Tsoflias	Sales Representative	3	285

Query to show the hierarchical level of managers and the employees who report to them:

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

    SELECT ManagerID, EmployeeID, Title, 1 AS EmployeeLevel

    FROM dbo.MyEmployees  WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1

    FROM dbo.MyEmployees AS e

        INNER JOIN DirectReports AS d

        ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

ORDER BY EmployeeLevel ;

Output:

ManagerID	EmployeeID	Title	EmployeeLevel
NULL	1	Chief Executive Officer	1
1	273	Vice President of Sales	2
273	16	Marketing Manager	3
273	274	North American Sales Manager	3
273	285	Pacific Sales Manager	3
285	286	Sales Representative	4
274	275	Sales Representative	4
274	276	Sales Representative	4
16	23	Marketing Specialist	4

More details about recursive CTE
http://technet.microsoft.com/en-us/library/ms175972.aspx