See if this helps

CREATE TABLE #Employees(Employee CHAR(1) NOT NULL PRIMARY KEY, ReportsTo CHAR(1))

INSERT INTO #Employees(Employee, ReportsTo)

SELECT 'A',NULL UNION ALL

SELECT 'B','A' UNION ALL

SELECT 'C','A' UNION ALL

SELECT 'D','C' UNION ALL

SELECT 'E','B' UNION ALL

SELECT 'F','B'

DECLARE @Start CHAR(1)

SET @Start='A';

WITH CTE AS

(SELECT 0 AS Depth, Employee, ReportsTo, CAST(Employee AS VARCHAR(MAX)) AS FullPath

FROM #Employees

WHERE Employee=@Start

UNION ALL

SELECT C.Depth+1, A.Employee,A.ReportsTo, C.FullPath + '\' + CAST(A.Employee AS VARCHAR(MAX))

FROM #Employees A

INNER JOIN CTE C ON A.ReportsTo = C.Employee)

SELECT Employee,ReportsTo

FROM CTE

ORDER BY FullPath

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537