Hierarchical Tree Structures in Database Tables

DECLARE @emp TABLE ( 
EmployeeID INT, 
FirstName VARCHAR(15), 
LastName VARCHAR(15), 
ReportsTo INT 
) 
 

DECLARE @ord TABLE ( 
OrderID INT, 
EmployeeID INT 
) 
 

INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo) 
SELECT 2,'Andrew','Fuller',NULL
UNION ALL SELECT 1,'Nancy','Davolio',2 
UNION ALL SELECT 3,'Janet','Leverling',2 
UNION ALL SELECT 4,'Margaret','Peacock',2 
UNION ALL SELECT 5,'Steven','Buchanan',2 
UNION ALL SELECT 8,'Laura','Callahan',2 
UNION ALL SELECT 6,'Michael','Suyama',5 
UNION ALL SELECT 7,'Robert','King',5 
UNION ALL SELECT 9,'Anne','Dodsworth',5  
--UNION ALL SELECT 10,'A','A',1  
--UNION ALL SELECT 11,'B','B',1  
--UNION ALL SELECT 12,'C','C',1   
--UNION ALL SELECT 13,'D','D',10  

--SELECT * FROM @emp 
 

INSERT INTO @ord (OrderID, EmployeeID)  
SELECT 10258,1 UNION ALL 
SELECT 10270,1 UNION ALL 
SELECT 10275,1 UNION ALL 
SELECT 10265,2 UNION ALL 
SELECT 10277,2 UNION ALL 
SELECT 10251,3 UNION ALL 
SELECT 10253,3 UNION ALL 
SELECT 10256,3 UNION ALL 
SELECT 10250,4 UNION ALL 
SELECT 10252,4 UNION ALL 
SELECT 10248,5 UNION ALL 
SELECT 10254,5 UNION ALL 
SELECT 10249,6 UNION ALL 
SELECT 10289,7 UNION ALL 
SELECT 10303,7 UNION ALL 
SELECT 10308,7 UNION ALL 
SELECT 10262,8 UNION ALL 
SELECT 10268,8 UNION ALL 
SELECT 10276,8 UNION ALL 
SELECT 10278,8 UNION ALL 
SELECT 10255,9 UNION ALL 
SELECT 10263,9  
 

;WITH cteNodes AS
(
 --initialization
 SELECT EmployeeID, ISNULL(ReportsTo, 0 ) AS ReportsTo, 0 AS EmpLevel
 FROM @emp

 UNION ALL

 ----recursive execution
 SELECT P.EmployeeID, ISNULL( N.ReportsTo, 0 ), P.EmpLevel + 1
 FROM cteNodes AS P
  INNER JOIN @emp AS N ON N.EmployeeID = P.ReportsTo
)
, cteEmployeePath AS
(
 SELECT DISTINCT Emp.EmployeeID
  , ISNULL( Emp.ReportsTo, 0 ) AS ReportsTo
  ,(
   SELECT '$' + CAST(ReportsTo AS VARCHAR(20) ) + '$'
   FROM cteNodes AS EmpParth
   WHERE EmpParth.EmployeeID = Emp.EmployeeID
   ORDER BY EmpLevel DESC
   FOR XML PATH('')
   ) 
   + '$' + CAST( Emp.EmployeeID AS VARCHAR(10) ) + '$' AS EmployeePath
   ,(
   SELECT '$0$'
   FROM cteNodes AS EmpParth
   WHERE EmpParth.EmployeeID = Emp.EmployeeID
   ORDER BY EmpLevel DESC
   FOR XML PATH('')
   ) AS EmpLevel
 FROM @emp AS Emp
)

SELECT
  (
  REPLICATE(' ', 3*( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) ) 
  + Emp.LastName
  + ' ' 
  + Emp.FirstName 
  ) AS EmployeeName
  , ( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) AS Level
  , SUM( CASE WHEN Ord.EmployeeID = Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_self
  , SUM( CASE WHEN Ord.EmployeeID != Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_sub
  , COUNT( Ord.OrderId ) AS total 
FROM cteEmployeePath 
 INNER JOIN @emp AS Emp ON Emp.EmployeeID = cteEmployeePath.EmployeeID
 INNER JOIN cteEmployeePath AS UnderEmployee ON UnderEmployee.EmployeePath Like cteEmployeePath.EmployeePath + '%'
 INNER JOIN @ord AS Ord ON Ord.EmployeeID = UnderEmployee.EmployeeID
GROUP BY Emp.EmployeeId, cteEmployeePath.EmpLevel, Emp.FirstName, Emp.LastName, cteEmployeePath.EmployeePath
ORDER BY cteEmployeePath.EmployeePath