SQL CTE를 이용한 재귀 쿼리

참조 : http://msdn.microsoft.com/ko-kr/magazine/cc163346.aspx
샘플 DB : northwind
샘플 DB 다운 : http://code.msdn.microsoft.com/northwind/Release/ProjectReleases.aspx?ReleaseId=1401

예제

;with RecursionCTE(EmployeeID, ReportsTo, FirstName)as
(
select EmployeeID, ReportsTo, FirstName
from employees
where employeeid =2
union all
select e.employeeid, e.reportsto, e.firstname
from employees e join
RecursionCTE r on e.reportsto = r.employeeid
)
select * from RecursionCTE option (maxrecursion 2)