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!