Showing related tags and posts accross the entire site.
-
Here is a query that uses a CTE to build an employee hierarchy and will display how many levels of employees are below a manager. WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ManagerID, HierarchyLevel) AS ( -- Base case SELECT EmployeeID, LastName, FirstName, ManagerID, 0 as HierarchyLevel...
-
Build hierarchy using CTE and recursion http://msdn.microsoft.com/en-us/library/ms186243.aspx WITH DirectReports ( [ManagerID] , [EmployeeID] , [RoleID] , [DepartmentID] , Level ) AS ( -- Root member definition SELECT e . [ManagerID] , e . [EmployeeID] , e . [RoleID] , e . [DepartmentID] , 0 AS Level...
-
Here's a good article written by Scott Mitchell that explains how to use Common Table Expressions (CTE) to retrieve hierarchical data with ms sql 2005. http://www.4guysfromrolla.com/ASPScripts/PrintPage.asp?REF=%2Fwebtech%2F071906-1.shtml Unlike a derived table, CTEs can be defined just once, yet...