How do I find an organization’s employee hierarchy using SQL?

 Finding an organization's employee hierarchy using SQL typically involves working with a table that stores information about employees and their relationships within the organization. The table usually includes columns like `employee_id`, `manager_id`, and other relevant information. Here's a general approach using a common scenario:

Assuming you have a table named `employees` with the columns `employee_id` and `manager_id`, where `manager_id` refers to the `employee_id` of the manager:

;with RECURSIVEEmpH as

(

select e.EmployeeID,e.Name,e.ManagerID, 1 as depth from dbo.Employee e where e.ManagerID is null

union all

select e.EmployeeID,e.Name,e.ManagerID,eh.depth+1 from dbo.Employee e  join RECURSIVEEmpH eh on e.ManagerID=eh.EmployeeId

)

select * from RECURSIVEEmpH

EmployeeID Name ManagerID depth

1 Mike 3 2

2 David 3 2

3 Roger NULL 1

4 Marry 2 3

5 Joseph 2 3

7 Ben 2 3