Here is the test table to work with. There are few levels of employee-manager relationship.

IF OBJECT_ID('Q25','U') IS NOT NULL BEGIN
    DROP TABLE Q25
END

CREATE TABLE Q25(
    EmployeeID INT NOT NULL Primary Key,
    EmployeeName VARCHAR(20) NOT NULL,
    ManagerID INT NULL
)
CREATE  NONCLUSTERED INDEX NI_ID_Name
ON Q25 (EmployeeID) INCLUDE (EmployeeName)

INSERT INTO Q25
VALUES
(11, 'Dan', NULL),
(22, 'John', 11),
(33, 'Kristina', 22),
(44, 'Jason', 33),
(55, 'Richard', 22),
(66, 'Adrian', 22),
(77, 'Matthew', 22),
(88, 'Chris', 33),
(2, 'Linda', 11),
(3, 'Sheena', 2),
(4, 'Peter', 2),
(5, 'Amelia', 3),
(6, 'Valieria', 3)

SELECT * FROM Q25

The solution is based on recursive queries. Here is the code to find out all people managed by EmployeeID=3

WITH ManagerEmployees (ManagerID, EmployeeID, EmployeeName)
AS
(
    --anchor query
    SELECT A.ManagerID, EmployeeID, EmployeeName FROM Q25 A
    WHERE EmployeeID  =3
    UNION ALL
    --reqursive query
    SELECT A.ManagerID, A.EmployeeID, A.EmployeeName FROM  Q25 A
    JOIN ManagerEmployees B
    ON A.ManagerID=B.EmployeeID
)
SELECT EmployeeID, EmployeeName FROM ManagerEmployees where EmployeeID<>3;

Here is the code to find out all managers for EmployeeID=3

WITH EmployeeManagers (EmployeeID, ManagerID, ManagerName)
AS
(
    --anchor query
    SELECT A.EmployeeID, A.ManagerID, B.EmployeeName FROM Q25 A
    JOIN Q25 B 
    ON A.ManagerID=B.EmployeeID
    WHERE A.EmployeeID  =3
    UNION ALL
    --reqursive query
    SELECT A. EmployeeID, A.ManagerID, B.EmployeeName FROM  Q25 A
    JOIN Q25 B
    ON A.ManagerID=B.EmployeeID
    JOIN EmployeeManagers C
    ON A.EmployeeID=C.ManagerID
)
SELECT ManagerName FROM EmployeeManagers

With SSRS reports we can call stored procedures and supply employeeID as filter parameter, so to round up the code:

CREATE PROCEDURE ReturnEmployees
(
    @EmployeeID INT
)
AS
BEGIN
    WITH ManagerEmployees (ManagerID, EmployeeID, EmployeeName)
    AS
    (
    	--anchor query
    	SELECT A.ManagerID, EmployeeID, EmployeeName FROM Q25 A
    	WHERE EmployeeID  = @EmployeeID
    	UNION ALL
    	--reqursive query
    	SELECT A.ManagerID, A.EmployeeID, A.EmployeeName FROM  Q25 A
    	JOIN ManagerEmployees B
    	ON A.ManagerID=B.EmployeeID
    )
    SELECT EmployeeID, EmployeeName FROM ManagerEmployees where EmployeeID<>@EmployeeID;
END
GO

CREATE PROCEDURE ReturnManagers
(
    @EmployeeID INT
)
AS
BEGIN
    WITH EmployeeManagers (EmployeeID, ManagerID, ManagerName)
    AS
    (
    	--anchor query
    	SELECT A.EmployeeID, A.ManagerID, B.EmployeeName FROM Q25 A
    	JOIN Q25 B 
    	ON A.ManagerID=B.EmployeeID
    	WHERE A.EmployeeID  =@EmployeeID
    	UNION ALL
    	--reqursive query
    	SELECT A. EmployeeID, A.ManagerID, B.EmployeeName FROM  Q25 A
    	JOIN Q25 B
    	ON A.ManagerID=B.EmployeeID
    	JOIN EmployeeManagers C
    	ON A.EmployeeID=C.ManagerID
    )
    SELECT ManagerID, ManagerName FROM EmployeeManagers 
END
GO

--to test it
DECLARE @myEmployeeID INT=3
EXEC ReturnEmployees @myEmployeeID;
EXEC ReturnManagers @myEmployeeID;

Based on the steps described in Converting a Table to a Hierarchical Structure I converted Igor's sample data using the hierarchyid data type introduced in SQL Server 2008:

IF OBJECT _ID('Q25Hierarchy','U') IS NOT NULL 
BEGIN
    DROP TABLE Q25Hierarchy
END

CREATE TABLE Q25Hierarchy
(
  OrgNode hierarchyid,
  EmployeeID INT NOT NULL,
  EmployeeName VARCHAR(20)
  CONSTRAINT PK_Q25Hierarchy_OrgNode
  PRIMARY KEY CLUSTERED (OrgNode)
);
INSERT INTO  Q25Hierarchy
    SELECT 0x	,11,'Dan'	UNION ALL
    SELECT 0x58	,22,'John'	UNION ALL
    SELECT 0x5AC0,55,'Richard'	UNION ALL
    SELECT 0x5B40,66,'Adrian'	UNION ALL
    SELECT 0x5BC0,77,'Matthew'	UNION ALL
    SELECT 0x5C20,33,'Kristina'	UNION ALL
    SELECT 0x5C2B,44,'Jason'	UNION ALL
    SELECT 0x5C2D,88,'Chris'	UNION ALL
    SELECT 0x68,2,'Linda'		UNION ALL
    SELECT 0x6AC0,3,'Sheena'	UNION ALL
    SELECT 0x6AD6,4,'Amelia'	UNION ALL
    SELECT 0x6ADA,5,'Valieria'	UNION ALL
    SELECT 0x6B40,6,'Peter'

ALTER TABLE Q25Hierarchy 
   ADD H _level AS OrgNode.GetLevel() ;
CREATE UNIQUE INDEX EmpBFInd 
   ON Q25Hierarchy(H _level, OrgNode) ;
GO
CREATE UNIQUE INDEX EmpIDs _unq ON Q25Hierarchy(EmployeeID) ;
GO

The following procedure can be used to return the requested information:

CREATE PROCEDURE ReturnHierarchy
(
    @EmployeeID INT
)
AS
BEGIN

DECLARE @CurrentEmployee hierarchyid, 
@H _Level SMALLINT

SELECT 
    @CurrentEmployee = OrgNode, 
    @H _Leve l =H _Level
FROM Q25Hierarchy
WHERE EmployeeID = @EmployeeID ;

; WITH cte AS
(
    SELECT 
    	OrgNode.ToString() AS LogicalNode,
    	H _Level, 
    	EmployeeName
    FROM Q25Hierarchy
    WHERE @CurrentEmployee.IsDescendantOf(OrgNode) = 1

    UNION ALL

    SELECT 
    	OrgNode.ToString() AS LogicalNode,
    	H _Level, 
    	EmployeeName
    FROM Q25Hierarchy
    WHERE OrgNode.GetAncestor(1)=@CurrentEmployee
)
SELECT LogicalNode,
H _Level, 
CASE 
    WHEN H _level < @H_Level THEN 'managed by' 
    WHEN H _level > @H_Level THEN 'manages' 
    ELSE '' END AS [position],
EmployeeName
FROM cte

END

The values in the LogicalNode column together with the H _Level column can be used to visualize the tree structure.

However, I would start with Igor's approach. I would not consider the nested set model since this concept is rarely used (as to my knowledge) therewith reducing the maintainability of the code.

Since it's rather easy to transform the adjacency list concept into the HierarchyID approach, I'd probably use the conversion as described in the article referenced above and compare the performance of both solutions if the number of employees or the level of hierarchies changes significantly.

Alternatively, in recent versions of SQL Server, you can accomplish this via a recursive Common Table Expression.

See this MSDN question and its answers. To quote:

WITH EmpCTE(empid, empname, mgrid, IsRoot)
AS
(

 -- Anchor Member (AM)
 SELECT empid, empname, mgrid, CASE WHEN mgrid IS NULL THEN 1 ELSE 0 END IsRoot
 FROM Employees
 WHERE empid = @empid

 UNION ALL

 -- Recursive Member (RM)
 SELECT M.empid, M.empname, M.mgrid, CASE WHEN M.mgrid IS NULL THEN 1 ELSE 0 END
 FROM Employees AS M
  JOIN EmpCTE AS E
   ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
GO

Since the requirement did not state that an employee can have more than one manager, I am going with a single table. No need for a manager table at this point. Should the need arise to maintain data that is specific only to managers, then a MANAGERS table would be incorporated. But for the requirements specified, even then a MANAGERS table would not be required to fulfill the report specs.

First let's create an EMPLOYEES table and populate it.

IF OBJECT_ID('Employees','U') IS NOT NULL 
        DROP TABLE Employees

CREATE TABLE Employees(
    EmpID INT NOT NULL Primary Key,
    EmpLastName VARCHAR(30) NOT NULL,
    EmpFirstName varchar(30) NOT NULL,
    EmpManager INT NULL
    )

INSERT INTO Employees VALUES (125876, 'Smith', 'John', NULL)
INSERT INTO Employees VALUES (590327, 'Doe', 'John', 203456)
INSERT INTO Employees VALUES (103948, 'Doe', 'Jane', 203456)
INSERT INTO Employees VALUES (203456, 'Ray', 'Doe', 203623)
INSERT INTO Employees VALUES (203623, 'Mee', 'Ray', 125876)
INSERT INTO Employees VALUES (234576, 'Simpleton', 'Homer', 345234)
INSERT INTO Employees VALUES (345234, 'Withered', 'Mister', 125876)
INSERT INTO Employees VALUES (314753, 'Nova', 'Bossa', 125876)
INSERT INTO Employees VALUES (213875, 'Nova', 'Super', 314753)
INSERT INTO Employees VALUES (283712, 'Chevy', 'Nova', 213875)
INSERT INTO Employees VALUES (123007, 'Jamesbond','Bond', 239234)
INSERT INTO Employees VALUES (239234, 'Emm','Anty', 125876)
INSERT INTO Employees VALUES (234574, 'Que', 'Pool', 239234)
INSERT INTO Employees VALUES (623089, 'Penny', 'Money', 239234)

Here is the query to pull the report data

DECLARE @WhichEmp as INT
SET @WhichEmp = 314753

SELECT 'EMPLOYEE' as EmpLevel, EmpID, EmpLastName, EmpFirstName
	FROM Employees WHERE EmpID = @WhichEmp
UNION ALL 
SELECT '    MANAGED BY' ,EmpID, EmpLastName, EmpFirstName
	FROM Employees WHERE EmpID = (SELECT EmpManager FROM Employees WHERE EmpID = @WhichEmp)
UNION ALL 
SELECT '  MANAGES' ,EmpID, EmpLastName, EmpFirstName
	FROM Employees WHERE EmpManager = @WhichEmp

RESULT::

EMPLOYEE           314753	Nova	Bossa
      MANAGED BY    125876  Smith	John
 MANAGES           213875	Nova	Super

To make pulling report data for a given employee trivial, lets create a stored procedure

CREATE PROCEDURE prEmpTree
    (@WhichEmp as int)
AS
    SET NOCOUNT ON

    SELECT 'EMPLOYEE' as EmpLevel, EmpID, EmpLastName, EmpFirstName
    	FROM Employees WHERE EmpID = @WhichEmp
    UNION ALL 
    SELECT '    MANAGED BY' ,EmpID, EmpLastName, EmpFirstName
    	FROM Employees WHERE EmpID = (SELECT EmpManager FROM Employees WHERE EmpID = @WhichEmp)
    UNION ALL 
    SELECT '  MANAGES' ,EmpID, EmpLastName, EmpFirstName
    	FROM Employees WHERE EmpManager = @WhichEmp;
GO

Then to get the Tree for a given employee we just

prEmpTree 314753

This technique will work for any size company. and SQL Server has no problem generating the result almost instantaneously. However maintaining the EmpManager field becomes cumbersome as the number of employees increases. As the number of employees increases it would be beneficial to add a DEPARTMENTS table and assign each employee to a department. Each department would be assigned a manager. If the manager of a department is changed the EMPLOYEES table would not have to be touched, only a single record in the DEPARTMENTS table would need to be updated. If an employee is moved to a different department, just update the department number in the EMPLOYEES table.

The query would have to be changed to join to the DEPARTMENTS table. Create a DEPARTMENTS Table and populate it. Add field EmpDeptID to the EMPLOYEES table and update the field to assign departments. Then edit the query to incorporate the join to the DEPARTMENTS table

SELECT 'EMPLOYEE' as EmpLevel, EmpID, EmpLastName, EmpFirstName
	FROM Employees WHERE EmpID = @WhichEmp
UNION ALL 
SELECT '    MANAGED BY', EmpID, EmpLastName, EmpFirstName
	FROM Employees 
	WHERE EmpID = (SELECT D.MgrID FROM Departments as D
					INNER JOIN Employees as E 
						ON D.DeptID = E.EmpDeptID
					WHERE E.EmpID = @WhichEmp)
UNION ALL 
SELECT '  MANAGES' , E.EmpID, E.EmpLastName, E.EmpFirstName
	FROM Employees as E 
	INNER JOIN Departments as D
		ON D.DeptID = E.EmpDeptID 
	WHERE D.MgrID = @WhichEmp

This will pull all of the Employee's subordinates even if the employee is manager of more than one department.

Chris,

I like your idea of Departments table (BTW, which is how we had it implemented in one of the systems I worked with). In any case, I wanted to see a more complex report with all managers structure starting from the current employee and all the employees he manages (as in a tree format). The first two answers are much closer to what I had in mind.

SQLServer Quiz 2011 - Human Resources Report

Solution:

There are enough business situations where we need to represent hierarchies in our data driven applications. One of the easiest hierarchies is the typical organization chart where employees report to their managers, the managers report to general managers and so on.

There are several ways of representing hierarchies in data driven applications; the most common is to use XML and self-referencing tables using recursive CTEs. Microsoft SQL Server 2008 introduces a new way to store hierarchies using a new built-in data type and set of functions.

I would definitely go with Lutz’s solution and my solution will be the same, therefore am not going to copy and paste it here.

Another approach for solving this quiz is with recursive queries using CTE. A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained. A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart. One of the most interesting applications of CTE is in solving hierarchical queries by using recursive CTEs. In SQL Server 2000 you would need to use temporary table and looping to solve the problem of hierarchical query, which can now be solved with 1 select in SQL Server 2005 and up.

Following is a sample CTE that can be used to solve this quiz. If we embed this query in the stored procedure as shown by Igor, we will get the desired output.

USE AdventureWorks;
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, LEVEL)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS LEVEL
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        LEVEL + 1
    FROM HumanResources.Employee AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, LEVEL
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR LEVEL = 0;

(Reference: CTE and hierarchical queries) from Quiz master blog.

Since another requirement in the quiz has mentioned that it should be properly indented and have some visual clues. To meet this need, we can also use a Recursive Hierarchy Group in SQL Reporting Services 2008. Here I am going to give high level steps on how we achieve this with the report model. This approach helps to create a recursive hierarchy group, which organizes data in a report to include multiple hierarchies’ level. This is very helpful when we want to display hierarchical data in the report, for example employees in an organizational chart.

Following are the steps taken for this solution:

• Create a new report
• Add a Data Source and Dataset
• Add a Table to the report and include dataset field
• Edit Group properties and reference a Recursive Parent
• Add custom format expressions
• Run the following code

--Run the following code

SELECT hr.EmployeeID,
hr.ManagerID, 
c.FirstName + ' ' + c.LastName AS 'Name', 
hr.Title
FROM  HumanResources.Employee AS hr 
INNER JOIN Person.Contact c ON hr.ContactID = c.ContactID

From the BIDS, perform the following steps.

• Add the table in the report model from the design view.

• Edit group properties and reference a recursive parent.

• Add custom format expressions: Indent Hierarchy and font style.

This is very high level steps and I have done this approach several times.

Conclusion:

Hierarchies can get much more complex than what has been demonstrated. This is a typical example showing hierarchies in an organization between employees and to whom they report to. Basically, I am trying to show that there are few different ways to resolve this quiz.

Thanks,

Abi Chapagai

And in Oracle this is easily accomplished using the CONNECT BY syntax, which SQL Server does not have yet, although there are articles on the internet about how to accomplish this using CTE & recursive functions, as some of the above solutions do. (Can't wait til SQL Server catches up with Oracle in this.)