Monday, January 25, 2010

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

No comments: