create table staff(employee int primary key, employee_name varchar(10),
supervisor int null references staff (employee))

insert staff 
values 
(1, 'groucho', Null),
(2, 'chico', 1),
(3, 'harpo', 2),
(4, 'zeppo', 2),
(5, 'moe', 1),
(6, 'larry', 5),
(7, 'curly', 5),
(8, 'shemp', 5),
(9, 'joe', 8),
(10, 'curly joe', 9);

select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o2.supervisor)
where o1.supervisor is not null

insert into #org_chart
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
and o1.supervisor is not null

select s.employee_name, supervises='supervises', e.employee_name
from #org_chart o join staff s on (o.supervisor=s.employee)
inner join staff e on (o.employee=e.employee)
where o.supervisor<>o.employee

select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o2.supervisor)
where o1.supervisor is not null

 SELECT ChartDepth = 1,
        Employee,
        Supervisor
   FROM dbo.Staff
;

 CREATE TABLE dbo.Staff
        (
        Employee        INT         NOT NULL, 
        Employee_Name   VARCHAR(10) NOT NULL,
        Supervisor      INT         NULL
        CONSTRAINT PK_Staff 
                   PRIMARY KEY CLUSTERED (Employee ASC),
        CONSTRAINT FK_Staff_ManagerMustBeAnEmployee 
                   FOREIGN KEY (Supervisor)
                   REFERENCES  dbo.Staff (Employee),
        CONSTRAINT CK_Staff_ManagerCannotBeSelf 
                   CHECK (Supervisor<>Employee)
        )
;
 INSERT INTO dbo.Staff 
        (Employee,Employee_Name,Supervisor)
 SELECT  1, 'Groucho'   , NULL  UNION ALL --1 UNION ALL
 SELECT  2, 'Chico'     , 1     UNION ALL
 SELECT  3, 'Harpo'     , 2     UNION ALL
 SELECT  4, 'Zeppo'     , 2     UNION ALL
 SELECT  5, 'Moe'       , 1     UNION ALL
 SELECT  6, 'Larry'     , 5     UNION ALL
 SELECT  7, 'Curly'     , 5     UNION ALL
 SELECT  8, 'Shemp'     , 5     UNION ALL
 SELECT  9, 'Joe'       , 8     UNION ALL
 SELECT 10, 'Curly Joe' , 9
;
WITH 
cteDirectReports AS 
( --=== This gets the top-level supervisors(s).
     -- This would be the "primer" for a loop.
 SELECT Employee, Supervisor, Employee_Name, EmployeeLevel = 1,
        HierarchicalPath = CAST('\'+CAST(Employee AS VARCHAR(10)) AS VARCHAR(4000))
   FROM dbo.Staff
  WHERE Supervisor IS NULL
  UNION ALL
 --==== This gets a level at a time using recursion of the CTE.
     -- This would be the "body" of a loop.
 SELECT e.Employee, e.Supervisor, e.Employee_Name, EmployeeLevel = d.EmployeeLevel + 1,
        HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Employee AS VARCHAR(10)) AS VARCHAR(4000))
   FROM dbo.Staff e
  INNER JOIN cteDirectReports d ON e.Supervisor = d.Employee 
) --=== This simply formats for display.  I wouldn't do this in real life, either.
 SELECT Employee,
        Supervisor,
        Employee_Name = SPACE((EmployeeLevel-1)*4) + Employee_Name,
        EmployeeLevel,
        HierarchicalPath 
   FROM cteDirectReports
  ORDER BY HierarchicalPath
;

Employee    Supervisor	Employee_Name	            EmployeeLevel   HierarchicalPath
1	        NULL	    Groucho	                    1	            \1
2	        1	            Chico	            2	            \1\2
3	        2	                Harpo	            3	            \1\2\3
4	        2	                Zeppo	            3	            \1\2\4
5	        1	            Moe	                    2	            \1\5
6	        5	                Larry	            3	            \1\5\6
7	        5	                Curly	            3	            \1\5\7
8	        5	                Shemp	            3	            \1\5\8
9	        8	                    Joe	            4	            \1\5\8\9
10	        9	                        Curly Joe   5	            \1\5\8\9\10