about sql

Question

what is recursive CTE in sql what is use of plz tell me

Jithil John · Answer

SQL SERVER – Simple Example of Recursive CTE Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). Here the result of CTE is repeatedly used to get the final resultset. The following example will explain in detail where I am using AdventureWorks database and try to find hierarchy of Managers and Employees. USE AdventureWorks GO WITH Emp_CTE AS ( SELECT EmployeeID  ContactID  LoginID  ManagerID  Title  BirthDate FROM HumanResources.Employee WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID  e.ContactID  e.LoginID  e.ManagerID  e.Title  e.BirthDate FROM HumanResources.Employee e INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID ) SELECT * FROM Emp_CTE GO In the above example Emp_CTE is a Common Expression Table the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the EmployeeID which don’t have ManagerID. Second query after UNION ALL is executed repeatedly to get results and it will continue until it returns no rows. For above e.g. Result will have EmployeeIDs which have ManagerID (ie EmployeeID of the first result). This is obtained by joining CTE result with Employee table on columns EmployeeID of CTE with ManagerID of table Employee. This process is recursive and will continue till there is no ManagerID who doesn’t have EmployeeID.

Munesh Sharma · Answer

http://sqlblog.com/blogs/adam_machanic/archive/2015/04/07/re-inventing-the-recursive-cte.aspx

Jignesh Trivedi · Answer

please refer my article http://www.c-sharpcorner.com/UploadFile/ff2f08/common-table-expression-cte-in-sql-server/

Nishant Mittal · Answer

Hi Read this https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/