Order by by using levels using sql Script

indian143

Hi All, I have two tables 1st table is Employee (Id, Name, Level) another one is EmployeeRelationShip (Id, EmployeeId, ManagerId), in Employee table all level 1's are Managers and Level 2's are workers. In EmployeeRelationShip table all Employees would have their ManagerId as Manager's Employee Id. Now for this structure I want to Order by Name for all managers, again under each manager I want to order all his Employees order by name again? Can anybody please help me in writing a script for me for this scenario? Any help may be a code snippet or a link or even a suggestion would be much helpful. Thanks in advance.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mathi Mani

Let us assume that you have following data in Employee table

Id Name Level
1 zzzz 1
2 nnnn 1
3 dddd 1
4 df 2
5 bc 2
6 za 2
7 az 2
8 ff 2
9 ef 2

and these data in EmployeeRelationShip table

Id EmployeeId ManagerId
1 4 1
2 5 1
3 6 2
4 7 2
5 8 3
6 9 3

This query will give results ordered by Manager Name and then by Employee Name:

select e.Name as ManagerName, b.Name as EmpName from
EmployeeRelationShip r inner join
Employee e on e.Id = r.ManagerId
inner join Employee b on b.Id = r.EmployeeId
order by e.Name, b.Name

And the output will be:

ManagerName EmpName
dddd ff
dddd ef
nnnn za
nnnn az
zzzz df
zzzz bc

Hope this provides an idea for you to solve your problem.

CHill60

You can use a recursive CTE to traverse the employee hierarchy - there a good example on the MSDN site - Recursive Queries Using Common Table Expressions[^] Yours is a little more complicated because you have the extra table, which could have been replaced by a single column on the Employee table "ReportsTo". It is also a little more complicated because you want the Managers in Name order followed by the Employees (for each Manager) also in Name order. This query seems to do what you want (I've used the sample data provided in the earlier response)

;WITH emps AS
(
SELECT e.Id, e.[Name], e.[Level], er.ManagerId as ReportsTo, em.Name as ManagerName
FROM Employee e
left outer join employeeRelationship er on er.EmployeeId = e.id
left outer join employee em on er.ManagerId = em.id
), Emp_CTE AS (
-- Anchor member - get only the managers
SELECT Id, [Name], [Level], ReportsTo,
MtoE = CAST(CAST(isnull(ManagerName,'') AS CHAR(30)) + '/' + CAST([Name] AS CHAR(30)) AS VARCHAR(MAX))
FROM emps e
WHERE ReportsTo IS NULL

UNION ALL

-- Recursive member - get the people who report to the managers
SELECT e.Id, e.\[Name\], e.\[Level\], e.Id as ReportsTo,
MtoE = MtoE + '/' + CAST(e.\[Name\] AS CHAR(30))

FROM emps e
INNER JOIN Emp\_CTE ecte ON ecte.Id = e.ReportsTo

)
SELECT Id, [Name], [Level]
FROM Emp_CTE
order by MtoE

Giving the results you asked for:

Id Name Level
3 dddd 1
9 ef 2
8 ff 2
2 nnnn 1
7 az 2
6 za 2
1 zzzz 1
5 bc 2
4 df 2

The explanation: The first CTE emps is just to simplify the table structure as I can't use a left outer join in the recursive CTE that follows it. If you run that query by itself you get the following

Id Name Level ReportTo ManagerName
1 zzzz 1 NULL NULL
2 nnnn 1 NULL NULL
3 dddd 1 NULL NULL
4 df 2 1 zzzz
5 bc 2 1 zzzz
6 za 2 2 nnnn
7 az 2 2 nnnn
8 ff 2 3 dddd
9 ef 2 3 dddd

The second CTE Emp_CTE is a recursive query that first gets only the Managers (marked as the "Anchor member" in the comment in the code). Those results are then fed into the "Recursive member" of the CTE until all of the employees have been processed. The "clever" bit is the derived column MtoE that "tracks" the

CHill60

OP asked for

Quote:

I want to Order by Name for all managers, again under each manager I want to order all his Employees order by name again?

Your query is only listing the workers