A former colleague of mine called me the other day with a following problem. She needed to write some reports against a self referencing table. Her initial thought was that she needed to create a number of temp tables, and fill them with the data for each hierarchy level. Her problem also was that she had no idea how deep the hierarchy went. When she got tired of running all kinds of select statements to find out the depth of that hierarchy, she called me for advice. Here is the sample code that I sent her.
Feel free to run this against the AdventureWorks database, and check out the results. The HumanResources.Employee table has an EmployeeID column, and a ManagerID column that references the EmployeeID within the hierarchy. The only catch is, this will not work in SQL 2000 or below.
1: WITH CorpStructure(ManagerID,EmployeeID, Title, Level)
2: AS
3: (
4: SELECT Emp.ManagerID, Emp.EmployeeID, Emp.Title, 1 as Level
5: FROM HumanResources.Employee as Emp
6: WHERE ManagerID is NULL
7: UNION ALL
8: SELECT Emp.ManagerID, Emp.EmployeeID, Emp.Title, Corp.Level + 1
9: FROM HumanResources.Employee as Emp INNER JOIN CorpStructure as Corp
10: ON Emp.ManagerID = Corp.EmployeeID
11: )
12: SELECT ManagerID, EmployeeID, Title, Level
13: FROM CorpStructure
Subscribe