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.<\/p>\n
Syntax<\/p>\n
[sql]
\nWITH cte_alias (column_aliases)
\nAS
\n(
\ncte_query_definition –initialization
\nUNION ALL
\ncte_query_definition2 –recursive execution
\n)
\nSELECT * FROM cte_alias
\n[\/sql]<\/pre>\n
Sample (from Root nodes to Leaf\u00a0 noes)<\/p>\n
[sql]
\nWITH Managers AS
\n(
\n–initialization
\nSELECT EmployeeID, LastName, ReportsTo
\nFROM Employees
\nWHERE ReportsTo IS NULL
\nUNION ALL
\n–recursive execution
\nSELECT e.employeeID,e.LastName, e.ReportsTo
\nFROM Employees e INNER JOIN Managers m
\nON e.ReportsTo = m.employeeID
\n)
\nSELECT * FROM Managers
\n[\/sql]<\/pre>\n
Sample (From\u00a0Leaf nodes to Root nodes), where u must specifie the leaves to include\/use.<\/p>\n
[sql]
\nWITH xEmployees AS
\n(
\n–initialization
\nSELECT\u00a0 EmployeeID, LastName, ReportsTo
\nFROM Employees
\nWHERE EmployeeID IN (55,98,65,12)\u00a0 — sample Leaf nodes to filter\/use
\nUNION ALL
\n–recursive execution
\nSELECT a.EmployeeID,a.LastName,a.ReportsTo
\nFROM Employees a INNER JOIN xEmployees m
\nON a.EmployeeID = m.reportsTo
\n)
\nSELECT Distinct EmployeeID, LastName, ReportsTo\u00a0 FROM xEmployees
\n[\/sql]<\/p>\n
!Importante to use “Distinct<\/span>” statement!<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40,9,104],"tags":[],"class_list":["post-1520","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","category-transact-sql","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1520"}],"version-history":[{"count":6,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1520\/revisions"}],"predecessor-version":[{"id":1523,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1520\/revisions\/1523"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1520"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}