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
[sql]
WITH cte_alias (column_aliases)
AS
(
cte_query_definition –initialization
UNION ALL
cte_query_definition2 –recursive execution
)
SELECT * FROM cte_alias
[/sql]
Sample (from Root nodes to Leaf noes)
[sql]
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
[/sql]
Sample (From Leaf nodes to Root nodes), where u must specifie the leaves to include/use.
[sql]
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
[/sql]
!Importante to use “Distinct” statement!
Home