Tell me, it is necessary to count the number of people in submission and display their list. Employees.ReportsTo indicates id , to whom this person submits.

 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
  • A subordinate subordinates also need to withdraw? then only recursion. - pegoopik

1 answer 1

You need to connect the table with itself, while specifying different aliases for the table from which you take the list of employees and for the table from which you take the list of subordinates.

Something 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