i




 
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
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.




Attached File(s)
.pdf  ReportDesignTips.pdf (Size: 570.65 KB / Downloads: 4)
Quote
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.”

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

Cheers!!!!!!
Rajesh Kumar Parbat
Quote


Possibly Related Threads...
Thread: Author Replies Views: Last Post
  What is CTE (Common Table Expression) in SQL? magnum_2007 0 1,707 02-15-2013 06:20 AM
Last Post: magnum_2007
  Get last inserted record sqlserver nisar87 3 7,409 10-20-2012 12:05 PM
Last Post: mayank13980
  Create Index in sqlserver nisar87 1 2,584 03-18-2011 01:10 PM
Last Post: raju
  Advantages of using CTE Shafiur Rahman 0 2,280 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,741 09-23-2010 10:18 AM
Last Post: arti.porwal20@gmail.com



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