Is there a way to get the complete hierarchy for a single Employee?

declare @EmployeeID int

select @EmployeeID = 12

;WITH

cteDirectReports AS

(

SELECT EmployeeID, ManagerID, EmployeeName, EmployeeLevel = 1,

HierarchicalPath = CAST('\'+CAST(EmployeeName AS VARCHAR(10)) AS VARCHAR(4000))

FROM dbo.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, EmployeeLevel = d.EmployeeLevel + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.EmployeeName AS VARCHAR(10)) AS VARCHAR(4000))

FROM dbo.Employee e

INNER JOIN cteDirectReports d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID,

ManagerID,

EmployeeName = SPACE((EmployeeLevel-1)*4) + EmployeeName,

EmployeeLevel,

HierarchicalPath

FROM cteDirectReports

where EmployeeID = @EmployeeID

ORDER BY HierarchicalPath

;

My quick and futile attempt above (which I understand why it does not work) only gets:

128 Megan4\Jim\Bob\Bill\Megan

The desired result would be:

1NULLJim1\Jim

31 Bob2\Jim\Bob

83 Bill3\Jim\Bob\Bill

138 Kim4\Jim\Bob\Bill\Kim

128 Megan4\Jim\Bob\Bill\Megan

Thanks for the post.