SQL Server 2019

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

create table Employee
(
  EmpId int,
  Name nvarchar(3),
  Salary money,
  MgrId int
);

insert into Employee (EmpId, Name, Salary, MgrId) values
(1, 'CEO', 10000, NULL),
(2, 'M2',   8000, 1),
(3, 'M3',   6000, 2),
(4, 'M4',   6000, 2),
(5, 'M5',   5000, 4),
(6, 'M6',   1000, 2),
(7, 'M7',    500, 5);

select * from Employee;


EmpId
Name
Salary
MgrId


1
CEO
10000.0000
null

2
M2
8000.0000
1

3
M3
6000.0000
2

4
M4
6000.0000
2

5
M5
5000.0000
4

6
M6
1000.0000
2

7
M7
500.0000
5


-- managers with employees
select e.Name as ManagerName,
       convert(money, 0) as Salary,
       e.EmpId
from Employee e
where exists ( select 'x'
               from Employee ee
               where ee.MgrId = e.EmpId);


ManagerName
Salary
EmpId


CEO
0.0000
1

M2
0.0000
2

M4
0.0000
4

M5
0.0000
5


-- managers with employees and their employee's salaries
with rcte as
(
  select e.Name as ManagerName,
         convert(money, 0) as Salary,
         e.EmpId
  from Employee e
  where exists ( select 'x'
                 from Employee ee
                 where ee.MgrId = e.EmpId)
union all
  select r.ManagerName,
         e.Salary,
         e.EmpId
  from rcte r
  join Employee e
    on e.MgrId = r.EmpId
)
select r.ManagerName,
       r.EmpId,
       r.Salary
from rcte r
order by r.ManagerName;


ManagerName
EmpId
Salary


CEO
1
0.0000

CEO
2
8000.0000

CEO
3
6000.0000

CEO
4
6000.0000

CEO
6
1000.0000

CEO
5
5000.0000

CEO
7
500.0000

M2
2
0.0000

M2
3
6000.0000

M2
4
6000.0000

M2
6
1000.0000

M2
5
5000.0000

M2
7
500.0000

M4
4
0.0000

M4
5
5000.0000

M4
7
500.0000

M5
5
0.0000

M5
7
500.0000


-- managers with their employee's salaries (aggregated)
with rcte as
(
  select e.Name as ManagerName,
         convert(money, 0) as Salary,
         e.EmpId as EmpId
  from Employee e
  where exists ( select 'x'
                 from Employee ee
                 where ee.MgrId = e.EmpId)
union all
  select r.ManagerName,
         e.Salary,
         e.EmpId
  from rcte r
  join Employee e
    on e.MgrId = r.EmpId
)
select r.ManagerName,
       sum(r.Salary) as TotalSalary
from rcte r
group by r.ManagerName;


ManagerName
TotalSalary


CEO
26500.0000

M2
18500.0000

M4
5500.0000

M5
500.0000

EmpId	Name	Salary	MgrId
1	CEO	10000.0000	null
2	M2	8000.0000	1
3	M3	6000.0000	2
4	M4	6000.0000	2
5	M5	5000.0000	4
6	M6	1000.0000	2
7	M7	500.0000	5

ManagerName	EmpId	Salary
CEO	1	0.0000
CEO	2	8000.0000
CEO	3	6000.0000
CEO	4	6000.0000
CEO	6	1000.0000
CEO	5	5000.0000
CEO	7	500.0000
M2	2	0.0000
M2	3	6000.0000
M2	4	6000.0000
M2	6	1000.0000
M2	5	5000.0000
M2	7	500.0000
M4	4	0.0000
M4	5	5000.0000
M4	7	500.0000
M5	5	0.0000
M5	7	500.0000