DECLARE @TempSuperviserID INT;
SET @TempSuperviserID = 222;
WITH AllEmployee AS
(
SELECT EmpID, EmpName, SuperviserName, SuperviserID
FROM Employee
WHERE SuperviserID = @TempSuperviserID
UNION ALL
SELECT e.EmpID, e.EmpName, e.SuperviserName, e.SuperviserID
FROM Employee e
INNER JOIN AllEmployee ecte ON ecte.EmpID = e.SuperviserID
)
SELECT * from AllEmployee
In order to get different type output you need to change
@TempSuperviserID value which is declared at the top.