SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE DATABASE_TABLE 
(
  Employee_Name nvarchar(255) PRIMARY KEY,
  Supervisor_Name nvarchar(255) NOT NULL
);

CREATE INDEX [ix_database_table_supervisor]
    ON DATABASE_TABLE (Supervisor_Name);
INSERT INTO DATABASE_TABLE
(Employee_Name, Supervisor_Name) VALUES 
  ('Alice','Dave')
, ('Olive','Dave')
, ('Barton','Dave')
, ('Almira','Jacob')
, ('Charles','Jacob')
, ('Davis','Jacob')
, ('Robert','Risha')
, ('Peter','Risha')
, ('Ethel','Risha')
, ('Isaac','Jospeh')
, ('Sophia','Jospeh')
, ('Rosa','Jospeh')
, ('Joshua','Dandy')
, ('Silas','Dandy')
, ('Fred','Dandy')
, ('Frank','Andrew')
, ('Howard','Andrew')
, ('Ralph','Andrew')
, ('Dennis','Henry')
, ('Alex','Henry')
, ('Floyd','Henry')
, ('Carlos','Nelson')
, ('Homer','Nelson')
, ('Harold','Nelson')
, ('Leo','Simon')
, ('Warren','Simon')
, ('Clifford','Simon')
, ('Martha','Casper')
, ('Hazel','Casper')
, ('Irene','Casper')
, ('Dave','Betsy')
, ('Jacob','Betsy')
, ('Risha','David')
, ('Jospeh','David')
, ('Dandy','Phillip')
, ('Andrew','Phillip')
, ('Henry','Harvey')
, ('Nelson','Harvey')
, ('Simon','Paul')
, ('Casper','Paul')
, ('Betsy','Joe')
, ('David','Joe')
, ('Phillip','Joe')
, ('Harvey','Joe')
, ('Paul','Joe')
45 rows affected

DECLARE @HeadName nvarchar(255);
SET @HeadName = 'David';

DECLARE @MaxLvl INT = 2;


WITH RCTE_DATA AS
(
   -- seeding the recursion
   SELECT
   t.Employee_Name AS Head_Name,
   t.Supervisor_Name AS Manager_Name,
   0 AS Lvl,
   t.Employee_Name, 
   t.Supervisor_Name
   FROM DATABASE_TABLE t
   WHERE t.Employee_Name = @HeadName

   UNION ALL
 
   -- looping the recursion  
   SELECT 
   c.Head_Name,
   c.Manager_Name,
   c.Lvl + 1,
   t.Employee_Name, 
   t.Supervisor_Name
   FROM RCTE_DATA c
   JOIN DATABASE_TABLE t
     ON t.Supervisor_Name = c.Employee_Name
   WHERE c.Lvl < @MaxLvl
)
SELECT 
Employee_Name, 
Supervisor_Name, 
Head_Name,
Manager_Name
FROM RCTE_DATA
WHERE Lvl = @MaxLvl


Employee_Name
Supervisor_Name
Head_Name
Manager_Name


Ethel
Risha
David
Joe

Peter
Risha
David
Joe

Robert
Risha
David
Joe

Isaac
Jospeh
David
Joe

Rosa
Jospeh
David
Joe

Sophia
Jospeh
David
Joe

Employee_Name	Supervisor_Name	Head_Name	Manager_Name
Ethel	Risha	David	Joe
Peter	Risha	David	Joe
Robert	Risha	David	Joe
Isaac	Jospeh	David	Joe
Rosa	Jospeh	David	Joe
Sophia	Jospeh	David	Joe