Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CTE in sqlserver 2008 with example
10-09-2012, 11:16 AM
Post: #1
CTE in sqlserver 2008 with example
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
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
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

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.

Attached File(s)
.pdf  ReportDesignTips.pdf (Size: 570.65 KB / Downloads: 4)
10-11-2012, 11:55 AM
Post: #2
RE: CTE in sqlserver 2008 with example
Nice sharing Vijay,hope will understand more if you provide some more examples

“A mind stretched by a new idea can never go back to its original dimension.”

05-23-2013, 07:11 PM
Post: #3
RE: CTE in sqlserver 2008 with example
Nice One

Rajesh Kumar Parbat

Possibly Related Threads...
Thread: Author Replies Views: Last Post
  What is CTE (Common Table Expression) in SQL? magnum_2007 0 1,763 02-15-2013 06:20 AM
Last Post: magnum_2007
  Get last inserted record sqlserver nisar87 3 7,509 10-20-2012 12:05 PM
Last Post: mayank13980
  Create Index in sqlserver nisar87 1 2,632 03-18-2011 01:10 PM
Last Post: raju
  Advantages of using CTE Shafiur Rahman 0 2,330 12-14-2010 06:47 AM
Last Post: Shafiur Rahman
  what is CTE (Common Table Partitioning) and how to use Paritioning table ? arti.porwal20@gmail.com 0 1,781 09-23-2010 10:18 AM
Last Post: arti.porwal20@gmail.com

User(s) browsing this thread: 1 Guest(s)