How to calculate the number of people reporting and remove their list MS SQL

[kalyn]

Indicate, the number of people on the line of command must be counted and their list removed. Employees.ReportsTo indicates idto whom this man reports.

Select
Employees.LastName,
Employees.FirstName,
Region.RegionDescription,
count(case when Employees.ReportsTo=Employees.EmployeeID then 1 end) as Count_of_employees,
Subordinates = STUFF((

SELECT ', ' + Employees.LastName

FROM Employees

WHERE Employees.ReportsTo = Employees.EmployeeID

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from Employees
left join EmployeeTerritories on
Employees.EmployeeID=EmployeeTerritories.EmployeeID
left join Territories on
EmployeeTerritories.TerritoryID=Territories.TerritoryID
left join Region on Territories.RegionID=Region.RegionID
group by Region.RegionDescription,Employees.LastName, Employees.FirstName

[Bogopo]

You need to connect the table to yourself, pointing out the different references to the table from which you take the staff list and the table from which you take the list of subordinates.

Like this:

Select  empl.LastName,
        empl.FirstName,
        Region.RegionDescription,
        count(empl1.EmployeeID) as Count_of_employees,
        Subordinates = STUFF((
          SELECT ', ' + empl2.LastName
          FROM Employees empl2
          WHERE empl2.ReportsTo = empl.EmployeeID
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from Employees empl

left join Employees empl1

on empl1.ReportsTo=empl.EmployeeID
left join EmployeeTerritories on
empl.EmployeeID=EmployeeTerritories.EmployeeID
left join Territories on
EmployeeTerritories.TerritoryID=Territories.TerritoryID
left join Region on Territories.RegionID=Region.RegionID
group by Region.RegionDescription,empl.EmployeeID, empl.LastName, empl.FirstName