Database

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

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

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

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

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?

;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

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

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

WITH TradingReceivableAccounts (AccountID, AccountCode, AccountName, GeneralAccount, AccountBranch, NatureOfAccount, AccountType, DebtorValue, CreditorValue, [Entry], [Description]) AS
(SELECT at.Account_ID, 
		at.Account_Code, 
		at.Account_Name1, 
		at.Account_Isleaf, 
		aib.AccountBranch_BranchID, 
		aib.AccountBranch_Natural, 
		aib.AccountBranch_Type, 
		ISNULL(aib.AccountBranch_LocalDebit, 0) AS DebtorValue, 
		ISNULL(aib.AccountBranch_LocalCredit, 0) AS CreditorValue,
		CAST(NULL AS NVARCHAR(250)) AS [Entry],
		N'TradingReceivableAccounts' AS [Description]
FROM AccountTree AS at INNER JOIN Account_InBranch AS aib 
								ON  at.Account_ID = aib.AccountBranch_AccountID 
								AND aib.AccountBranch_Natural = 1
								AND aib.AccountBranch_Type = 2
								AND aib.AccountBranch_BranchID = 1
								        
UNION ALL
SELECT ac.Account_ID,
	   ac.Account_Code, 
	   ac.Account_Name1, 
	   ac.Account_Isleaf, 
	   CAST(0 AS INT) AS AccountBranch, 
	   CAST(0 AS BIT) AS NatureOfAccount, 
	   CAST(0 AS TINYINT) AS AccountType,  
	   jd.JournalDet_Debit,
	   jd.JournalDet_Credit,
	   CAST(jh.Journal_Code AS NVARCHAR(250)),
	   N'TradingReceivableAccounts' AS [Description]
FROM  TradingReceivableAccounts AS CTE INNER JOIN AccountTree AS ac
		ON CTE.AccountID = ac.Account_ID
		INNER JOIN Journal_Details AS jd
	    ON jd.JournalDet_AccountID = ac.Account_ID
	    INNER JOIN Journal_Head AS jh
	    ON  jh.Journal_ID = jd.JournalDet_HeadID
	    AND jh.Journal_BranchID = CTE.AccountBranch
		AND jh.Journal_Date < N'02/18/2016')

use Sandbox
if exists (select * from sysobjects where id = object_id('dbo.Employees') )
	drop table "dbo"."Employees"
GO
CREATE TABLE "Employees" (
	"EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
	"LastName" nvarchar (20) NOT NULL ,
	"FirstName" nvarchar (10) NOT NULL ,
	"Title" nvarchar (30) NULL ,
	"ReportsTo" "int" NULL 
)

INSERT "Employees"  VALUES('Davolio','Nancy','Sales Representative',2),
		('Fuller','Andrew','Vice President, Sales',NULL),
		('Leverling','Janet','Sales Representative',2),
		('Peacock','Margaret','Sales Representative',2),
		('Buchanan','Steven','Sales Manager',2),
		('Suyama','Michael','Sales Representative',5),
		('King','Robert','Sales Representative',5),
		('Callahan','Laura','Inside Sales Coordinator',2),
		('Dodsworth','Anne','Sales Representative',5)

;WITH Emp_CTE AS (
	-- Anchor member - initialise the Level of recursion to 1 
	-- and start the Manager-to-Employee column MtoE
	SELECT EmployeeID, ReportsTo, LastName, FirstName, Title, 1 as RLevel
	,MtoE = CAST(isnull(ReportsTo,0) AS VARCHAR(MAX)) + '/' + CAST(EmployeeID AS VARCHAR(MAX))
	FROM Employees WHERE ReportsTo IS NULL

	UNION ALL

	-- Recursive member - and add on to MtoE the paths we are walking
	SELECT e.EmployeeID, e.ReportsTo, e.LastName, e.FirstName, e.Title, RLevel + 1
	,MtoE=MtoE + '/' + CAST(e.EmployeeID AS VARCHAR(MAX))
	FROM Employees e
	INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ReportsTo
)
SELECT EC.EmployeeID, EC.ReportsTo, EC.LastName, EC.FirstName, EC.Title, RLevel, MtoE
FROM Emp_CTE EC

Emp.Id  Manager LastName    FirstName   Job Title                Level  RecursionPath
2	NULL	Fuller	    Andrew	Vice President, Sales	 1	0/2
1	2	Davolio	    Nancy	Sales Representative	 2	0/2/1
3	2	Leverling   Janet	Sales Representative	 2	0/2/3
4	2	Peacock	    Margaret	Sales Representative	 2	0/2/4
5	2	Buchanan    Steven	Sales Manager	         2	0/2/5
8	2	Callahan    Laura	Inside Sales Coordinator 2	0/2/8
6	5	Suyama	    Michael	Sales Representative	 3	0/2/5/6
7	5	King	    Robert	Sales Representative	 3	0/2/5/7
9	5	Dodsworth   Anne	Sales Representative	 3	0/2/5/9