SQL Server 2005 Books Online (November 2008)
Using Common Table Expressions

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name

Example

The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, NumberOfOrders, and MaxDate) and is defined as the total number of sales orders and the most recent sales order date in the SalesOrderHeader table for each salesperson. When the statement is executed, the CTE is referenced two times: one time to return the selected columns for the salesperson, and again to retrieve similar details for the salesperson's manager. The data for both the salesperson and the manager are returned in a single row.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

Here is a partial result set:

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268         48             2004-06-01 273       NULL           NULL
275         450            2004-06-01 268       48             2004-06-01
276         418            2004-06-01 268       48             2004-06-01
277         473            2004-06-01 268       48             2004-06-01
See Also

Other Resources

WITH common_table_expression (Transact-SQL)
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
CREATE VIEW (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance
Tags :


Community Content

Manivannan.D.Sekaran
Structure of a CTE

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

[

, expression_name [ ( column_name [,...n] ) ]
AS

( CTE_query_definition )

..

]

On single query we can have more than one expression. Each expression will be seperated by comma. You can use the current expression result on following expressions (as nested).

For simple CTE expression quiers you can apply the Insert/Update/Delete/Select statements to run the CTE.

(example) 

Create table #Duplicates
(
 Id int,
 Name varchar(10)
)
 
Go
 
Insert Into #Duplicates Values(1,'One');
Insert Into #Duplicates Values(1,'One');
Insert Into #Duplicates Values(2,'Two');
Insert Into #Duplicates Values(3,'Three');
Insert Into #Duplicates Values(4,'Four');
Insert Into #Duplicates Values(4,'Four');
 
Go
 
Select * from #Duplicates
 
Go
 
--To print the Duplicate entries
;With CTE
as
(
 Select *, Row_Number() Over(Partition By Id Order By Id,Name) Rowid
 From #Duplicates
)
Select * from CTE Where RowId>1
 
Go
 
--To remove the Duplicate entries
;With CTE
as
(
 Select *, Row_Number() Over(Partition By Id Order By Id,Name) Rowid
 From #Duplicates
)
Delete from CTE Where RowId>1;
 
Go
 
Select * from #Duplicates
 
Go
 
Drop table #Duplicates
Tags : cte

Patrick Gallucci MSFT
Using a common table expression in a view.
USE [AdventureWorks]
GO
CREATE VIEW HumanResources.vEmployeeAddress
AS  WITH    StateProvinceCountryRegion ( StateProvinceID, StateProvinceCode, CountryRegionCode, IsOnlyStateProvinceFlag, StateProvinceName, TerritoryID, CountryRegionName, TerritoryName )
              AS ( SELECT   Person.StateProvince.StateProvinceID AS StateProvinceID,
                            Person.StateProvince.StateProvinceCode AS StateProvinceCode,
                            Person.StateProvince.CountryRegionCode AS CountryRegionCode,
                            Person.StateProvince.IsOnlyStateProvinceFlag AS IsOnlyStateProvinceFlag,
                            Person.StateProvince.Name AS StateProvinceName,
                            Person.StateProvince.TerritoryID AS TerritoryID,
                            Person.CountryRegion.Name AS CountryRegionName,
                            Sales.SalesTerritory.Name AS TerritoryName
                   FROM     Person.StateProvince
                            INNER JOIN Person.CountryRegion ON Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode
                            INNER JOIN Sales.SalesTerritory ON Person.StateProvince.TerritoryID = Sales.SalesTerritory.TerritoryID
                 )
    SELECT  HumanResources.Employee.EmployeeID,
            HumanResources.Employee.NationalIDNumber,
            HumanResources.Employee.ContactID,
            HumanResources.Employee.LoginID,
            HumanResources.Employee.ManagerID,
            HumanResources.Employee.Title,
            HumanResources.Employee.BirthDate,
            HumanResources.Employee.MaritalStatus,
            HumanResources.Employee.Gender,
            HumanResources.Employee.HireDate,
            HumanResources.Employee.SalariedFlag,
            HumanResources.Employee.VacationHours,
            HumanResources.Employee.SickLeaveHours,
            HumanResources.Employee.CurrentFlag,
            Person.Address.AddressID,
            Person.Address.AddressLine1,
            Person.Address.AddressLine2,
            Person.Address.City,
            Person.Address.StateProvinceID,
            Person.Address.PostalCode,
            StateProvinceCountryRegion.StateProvinceCode,
            StateProvinceCountryRegion.CountryRegionCode,
            StateProvinceCountryRegion.IsOnlyStateProvinceFlag,
            StateProvinceCountryRegion.StateProvinceName,
            StateProvinceCountryRegion.TerritoryID,
            StateProvinceCountryRegion.CountryRegionName,
            StateProvinceCountryRegion.TerritoryName
    FROM    HumanResources.Employee
            INNER JOIN HumanResources.EmployeeAddress ON HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID
            INNER JOIN Person.Address ON HumanResources.EmployeeAddress.AddressID = Person.Address.AddressID
            INNER JOIN StateProvinceCountryRegion ON Person.Address.StateProvinceID = StateProvinceCountryRegion.StateProvinceID;
GO
SELECT  *
FROM    [AdventureWorks].HumanResources.vEmployeeAddress;
GO
Tags : cte

Stanley Roark
Adjacency List model (parent / child) using CTE Recursion.
For a full explanation of how the recursive part work with examples see my blog: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/11/common-table-expressions-cte-s-how-it-works-how-recursion-works-using-with-adjacency-list.aspx

--
--
create table adjacency_list ( 
    parent_extractedword_id int not null, 
    child_extractedword_id int not null, 
        constraint pk_adjacency_list primary key clustered( parent_extractedword_id, child_extractedword_id ) 

) 


create table extractedword ( 
    id int not null constraint pk_extractedword primary key clustered, 
    word varchar(50) not null 

) 

insert extractedword( id, word ) values( 11,'demo') 
insert extractedword( id, word ) values( 12,'on') 
insert extractedword( id, word ) values( 13,'cte') 
insert extractedword( id, word ) values( 14,'recursion') 


insert extractedword( id, word ) values( 21,'does') 
insert extractedword( id, word ) values( 22,'this') 
insert extractedword( id, word ) values( 23,'rock') 
insert extractedword( id, word ) values( 24,'ya') 
insert extractedword( id, word ) values( 25,'boat?') 


insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 11, 12 ) 
insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 12, 13 ) 
insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 13, 14 ) 


insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 21, 22 ) 
insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 22, 23 ) 
insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 23, 24 ) 
insert adjacency_list ( parent_extractedword_id, child_extractedword_id ) values( 24, 25 ) 


;WITH WordStruct( org_parent_id, child_id, recurse_depth ) 
AS ( 
    -- The anchor is the starting point for possible recursion 
    SELECT org_parent_id = parent_extractedword_id, 
           child_id = child_extractedword_id, 
           recurse_depth = CAST( 0 as tinyint) 
    FROM adjacency_list l1 
    WHERE NOT EXISTS( SELECT *-- The Anchor should only contain root PARENT's 
                      FROM adjacency_list l2 
                      WHERE l2.child_extractedword_id = l1.parent_extractedword_id ) 
    UNION ALL 
    SELECT org_parent_id = l1.child_id, 
           child_id = l2.child_extractedword_id, 
           recurse_depth = recurse_depth + cast( 1 as tinyint) 
    FROM WordStruct l1 
        INNER JOIN adjacency_list l2 on l2.parent_extractedword_id = l1.child_id 

) 

select e.id, e.word, ws.*, 
       case when org_parent_id=e.id then 'ROOT' else 'CHILD.DEPTH=' + CAST( recurse_depth + 1 as varchar(5)) end 
from WordStruct ws 
    INNER JOIN extractedword e on e.id = ws.child_id 
                             or ( ws.recurse_depth = 0 -- Do this to get the desc for the root parent 
                              and e.id = ws.org_parent_id ) 
order by ws.org_parent_id, ws.child_id

Page view tracker