Contact Me

Nirav Prabtani

Mobile : +91 738 308 2188

Email :

Nirav Prabtani

Monday, 21 April 2014

CTE Query in sql server.


Common Table Expressions (CTE) query is very useful to reduce query length as well complexity.
Generally we are using Joins for retrieving records from multiple tables, It is difficult and complex little bit than CTE queries.
A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE query returns us a single query from multiple joins query so we can easily deal with it and get more expected result in less effort.

Using the code

It is very easy to write CTE query.
A Common Table Expression defines with three components.
  1. CTE name after WITH phrase
  2. Column list (It is not mandatory)
  3. Final query (appears within parentheses after the AS keyword)
Let see Format of CTE query
WITH CTEname(Columnlist optional) AS(..sql query..)
it returns all columns of sql query which has been written in parenthesis..
WITH SampleData AS(SELECT e1.column2 AS EmpName,e2.column2 AS Department FROM table1 e1 INNER JOIN table2 e2 ON e1.Column3=e2.column1) 
select * from SampleData  
it returns two columns having name EmpName and Department and it act like single and simple query.
We can use multiple CTE query as well
with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)
    ,samp1 as(
            select * from samp
            select 'Code' as Text1 ,'project' as Text2
select * from samp1  
we can define column name as well like this.
WITH CTEname (columnlist) as (....) 
 with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)

    ,samp1(MyColumn1,MyColumn2) as(
            select * from samp
            select 'Code' as Text1 ,'project' as Text2
select * from samp1  
This query returns output of both queries.. 

Points of Interest

I have retrieved dates between date interval from first date to second date like this..
     SELECT CAST('23 Mar 2014' AS DATETIME) AS dt
     SELECT DATEADD(dd, 1, dt)
      FROM CTEQuery s
      WHERE DATEADD(dd, 1, dt) <= CAST('26 Jun 2014' AS DATETIME)
select * from CTEQuery 
It returns all the date row wise between 23 Mar 2014 to 26 Jun 2014.... Smile | :)

  • 20 Jan 2014, initial level.. 

No comments :

Post a Comment