http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/01/17/cte-command-in-sql-server/ Printed 2017/03/23 12:26AM
CTE command In Sql Server
Common table expression (CTE):- Common table expression or CTE can be described as the temporary, named record set return by the execution of the query (insert, delete, update, select or update view statement). It is not stored as an object in the database and it last only till the execution of the SQL Query.It also can reference itself. It can be referenced multiple times. The SQL Scripts of creation of all the tables and their insert data statement is given below:-
Query for creation of the database used in this article and then using it.
Create database CTE_Learning
Query to used the database created above
SQL Query for creation of the table Employee
Create table Employee (Employeeid int identity(1,1), EmpFname nvarchar(150), EmpLname nvarchar(150), Managerid int, regdate datetime)
SQL Query for creation of the table Project
Create table Project (Projectid int identity(1,1), Projectname nvarchar(150))
SQL Query for creation of the table Empproj
Create table Empproj (Empprojid int identity(1,1), Employeeid int, Projectid int)
Query for inserting the data in the table Employee
Insert into Employee(EmpFname, EmpLname, Managerid, regdate) Select 'Samir', 'Sethi', 0, getdate() union all Select 'Vivek', 'Johari', 1, getdate() union all Select 'Avinash', 'Dubey', 2, getdate() union all Select 'Sunny', 'Rana', 3, getdate() union all Select 'Himanshu','Aggarwal',3,getdate()
Query for inserting the data in the table Project
Insert into Project (Projectname) Select 'Project1' Union all Select 'Project2' union all Select 'Project3'Query for inserting the data in the table Empproj
Insert into Empproj (Employeeid, Projectid) Select 1,1 Union all select 1, 2 Union all select 1,3 Union all select 2,1 Union all Select 2,2 Union all Select 3,1 Union all Select 4,2 The Common table expression has the following syntax:
With Expressionname (Column list)AS( CTE query definition)
Here, Expressionname is the name of the CTE, Column list is the name of the unique column names which are return as the record set through the execution of the CTE query and CTE query definition is the SQL query which we are going to use in the CTE.
Suppose, we want to get the recordset which contains the details of the employees who are attacted to more than 1 projects then we can use the CTE as given below:-
With CTE_Projinfo (employeeid, total_projects) As( select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1)
select e.employeeid , e.empfname + ' ' + e.empLname, e.regdate from employee e inner join CTE_Projinfoon e.employeeid = CTE_Projinfo.employeeid
Here CTE_Projinfo is the CTE expression name employeeid, total_projects are the names of the columns return by the execution of the CTE query
select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1
In the above example, we have used the CTE "CTE_Projinfo " to get the details of the employees by using the inner join with the table employee.
Also we can includes multiple CTEs within a WITH ClauseFor example,
With CTE_Projinfo (employeeid, total_projects) As( select employeeid, count(employeeid) from empproj group by employeeid having count(employeeid)>1),
CTE_Empproject (employeeid, projectname)AS( Select CTE_Projinfo.employeeid,Projectname from CTE_Projinfo inner join Empprojon CTE_Projinfo.Employeeid=Empproj.employeeid inner join Project on Empproj.projectid=Project.projectid)
select e.employeeid , e.empfname + ' ' + e.empLname, e.regdate,CTE_Empproject.projectname from employee e inner join CTE_Empproject on e.employeeid = CTE_Empproject.employeeid
Here we have includes two CTEs expressions CTE_Projinfo and CTE_Empproject within a same With caluse. In this example, we have seperated the two CTEs with the help of ",". Since a CTE can reference itself also, we can use the CTE to get the hierarchy of an organization.
For example, we can use the following CTE to get the hierarchical data of the employees of an organisation. With CTE_managerlevel (Employeeid,employeename,managerid,joblevel) as( select e.employeeid , e.empfname + ' ' + e.empLname, e.Managerid,0 from employee e where e.employeeid=1 UNION ALL select m.employeeid , m.empfname + ' ' + m.empLname, m.Managerid,CTE_managerlevel.joblevel+1 from employee m inner join CTE_managerlevel on m.Managerid=CTE_managerlevel.employeeid)
select Employeeid,employeename,managerid,joblevel from CTE_managerlevel