I managed to figure out a more dynamic way of doing this using a couple of recursive CTE's one to traverse downt he hierarchy the second to traverse up the heirarchy.

Heres the SQL code.

SET STATISTICS TIME ON;

WITH DirectReports(Name,Manager, EmployeeID, ManagerId, EmployeeLevel)

AS

(SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),

Convert(varchar, NULL) Manager,

e.EmployeeID,

convert(int,Null) ManagerId,

0

FROM dbo.MyEmployees AS e

WHERE e.ManagerID IS NULL

UNION ALL

SELECT

CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),

Convert(varchar,d.Name) name,

e.EmployeeID,

convert(int,d.EmployeeID) as ManagerId,

EmployeeLevel + 1

FROM dbo.MyEmployees AS e

JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

), X AS

(

Select Row_Number() OVER (ORDER BY EmployeeId) id,EmployeeID, Name, ManagerId, EmployeeLevel

From DirectReports

UNION ALL

SELECT

id,

x.EmployeeId,

CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),

e.ManagerId,

EmployeeLevel-1

From X

JOIN dbo.MyEmployees as e on e.EmployeeId=x.ManagerId

)

Select id, EmployeeId,Max([0]),Max([1]),Max([2]),Max([3]),Max([4])

from (Select *

FROM (Select id,d.EmployeeId,d.ManagerId,d.Name, d.EmployeeLevel,d.EmployeeLevel c --,Sort

from x d

) T

PIVOT (MAX(Name) For EmployeeLevel IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9])) p) piv

group by id,EmployeeID

It takes 3ms to run on a 9 row table, on a local DB.

In theory it should work for any Parent Child Hierarchy as long as you know the number of levels.