CODEWITHSUNDEEP
sqlsql-server
challengeAccepted
challengeAccepted

Reputation: 7590

group by for Manager and Reps in Employee table

I have a table Employees with Managers and Reps in it. I would want a stored procedure that can get Manager 1 (alphabetic order) and then all Reps under that Manager1 (in alphabetic order) and then Manager 2 and all Reps under the Manager2.

There is a Column 'Manager' for all Employees with EmployeeID of Manager in that column for each Rep and null for Managers. And there is also a column ismanager which will be 1 for Managers and 0 for reps.

I tried doing it but the logic is somewhere incorrect. this is in SQL Server 2005.

Thank you in advance!!

Select * from Employees
groupby IsManager, EmployeeID

Upvotes: 1

Views: 523

Answers (2)

paul
paul

Reputation: 22001

select  case when isManager=1 then man.name else 'Unmanaged' end as managerName,
        emp.name
from    employees emp left join employees man on emp.manager = man.employeeId
order by case when isManager=1 then 0 else 1 end, 
        case when isManager=1 then man.name else 'Unmanaged' end,
        emp.name

Upvotes: 0

Hogan
Hogan

Reputation: 70523

What you want to do is order by the manager id and then order by ismanager desc. Since the manager does not have his own id in the manager column you have use a case statement to fix that. Here are two examples of how to do it in SQL:

WITH fixup AS
(
   SELECT CASE MANAGER = 0 THEN EMPLOYEEID ELSE MANAGER END as ManGroup, *
   FROM Employees
)
SELECT * from fixup
ORDER BY ManGroup, ismanager DESC

or

SELECT * 
FROM Employees
ORDER BY CASE MANAGER = 0 THEN EMPLOYEEID ELSE MANAGER END, ismanager DESC

nb -- not tested might have typos.

Upvotes: 1

Related Questions