Given the example , hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused.
Syntax
WITH cte_alias (column_aliases) AS ( cte_query_definition --initialization UNION ALL cte_query_definition2 --recursive execution ) SELECT * FROM cte_alias
Sample (from Root nodes to Leaf noes)
WITH Managers AS ( --initialization SELECT EmployeeID, LastName, ReportsTo FROM Employees WHERE ReportsTo IS NULL UNION ALL --recursive execution SELECT e.employeeID,e.LastName, e.ReportsTo FROM Employees e INNER JOIN Managers m ON e.ReportsTo = m.employeeID ) SELECT * FROM Managers
Sample (From Leaf nodes to Root nodes), where u must specifie the leaves to include/use.
WITH xEmployees AS ( --initialization SELECT EmployeeID, LastName, ReportsTo FROM Employees WHERE EmployeeID IN (55,98,65,12) -- sample Leaf nodes to filter/use UNION ALL --recursive execution SELECT a.EmployeeID,a.LastName,a.ReportsTo FROM Employees a INNER JOIN xEmployees m ON a.EmployeeID = m.reportsTo ) SELECT Distinct EmployeeID, LastName, ReportsTo FROM xEmployees
!Importante to use “Distinct” statement!
Home