SQL CTE (Common Table Expression)

CTE stands for Common Table Expressions in SQL Server. Common Table Expressions (CTE) were added into standard SQL to ease a variety of SQL queries for which a derived table was just not appropriate.

Common Table Expressions are a named set that lives only for the length of a query (similar to a temporary table).

Inside the execution scope of a single SELECT, INSERT, DELETE, or UPDATE statement that uses all or some of the Common table expression fields, SQL Server CTE. The multiple CTE definition can be defined using UNION, UNION ALL, INTERSECT, or EXCEPT.

You can also add a CTE to the new MERGE statement.

The basic rules to use this SQL Server CTE are:

A CTE can reference itself, as well as previously established common table expressions, but not the next one (forward referencing) We cannot refer to tables on a remote server in SQL common table expression.

Syntax:

WITH Expression_Name (Column_Name1, ColumnName2,.....ColumnNameN)
AS
(Define) -- Write a query

SELECT Column_Name1, ColumnName2,.....ColumnNameN
FROM Expression_Name -- Or, CTE Name

In this syntax:

• The CTE's name comes first, followed by an optional column list.
• Second, define a query that returns a result set inside the WITH clause's body. If you don't mention the column list following the CTE name, the CTE query definition's select list will be used as the CTE's column list.
• Third, treat the CTE like a table or view in the SELECT, INSERT, UPDATE, or DELETE statement.

Example 1: In this simple example, we will show you how to write a simple CTE in SQL Server.

WITH Total_Sale 
AS
(
SELECT [Occupation]
      ,[Education]
      ,SUM([YearlyIncome]) AS Income
      ,SUM([Sales]) AS Sale
  FROM [Employee Table]
  GROUP BY [Education], [Occupation]
)
SELECT * FROM Total_Sale

Example 2: We will use the film and rental tables from the sample database for the demonstration.

WITH cte_film AS (
    SELECT 
        film_id, 
        title,
        (CASE 
            WHEN length < 30 THEN 'Short'
            WHEN length < 90 THEN 'Medium'
            ELSE 'Long'
        END) length    
    FROM
        film
)
SELECT
    film_id,
    title,
    length
FROM 
    cte_film
WHERE
    length = 'Long'
ORDER BY 
    title; 

Example 3: Let's take a look at a CTE example to drive this home:

WITH Simple_CTE
AS (
   SELECT dd.CalendarYear
      ,fs.OrderDateKey
      ,fs.ProductKey
      ,fs.OrderQuantity * fs.UnitPrice AS TotalSale
      ,dc.FirstName
      ,dc.LastName
   FROM [dbo].[FactInternetSales] fs
   INNER JOIN [dbo].[DimCustomer] dc ON dc.CustomerKey = fs.CustomerKey
   INNER JOIN [dbo].[DimDate] dd ON dd.DateKey = fs.OrderDateKey
   )
SELECT *
FROM Simple_CTE

Advantages of CTE

• CTE enhances readability and enables complex query maintenance simple.
• Queries that mention themselves are known as recursive queries. When querying hierarchical data such as an organisation chart or a bill of materials, recursive queries come in useful.
• Though it has a similar purpose, It can be used in place of a view, but the description will not be saved in the metadata.
• Functions, stored procedures, triggers, and even views can define CTE.

Can be referenced multiple times in a query.

Temporary result sets generated inside the execution scope of a single SELECT, INSERT, UPDATE, DELETE, OR CREATE VIEW statement are known as common table expressions. It's similar to a derived table that only exists for the duration of the query session.

Allow for grouping by a column obtained from a scalar subset.

The query can be broken down into simple, logical building pieces that can then be utilized to create more sophisticated CTEs until the desired result set is obtained.

To build an initial result set, use CTEs in conjunction with window functions and a second select statement.

Example 1: sql cte case statement used with databases:

WITH order_size_table AS (
    SELECT
        order_id,
        order_date,
        (CASE
            WHEN freight < 50 THEN 'Small'
            WHEN freight < 100 AND freight > 50 THEN 'Medium'
            ELSE 'Large'
        END) order_size
    FROM
        orders
)
SELECT
	order_id,
	order_date,
    order_size
FROM
    order_size_table
WHERE
    order_size = 'Large'
ORDER BY
    order_date;

Output :

order_id  order_date.     order_size
10255     "1996-07-12"    "Large"
10258     "1996-07-17"    "Large"
10263     "1996-07-23"    "Large"

Example 2: case statement with cte:

First and all, you are utilizing CASE WHEN. It's designed to be used in select clauses to return a single value rather than a table. It's not meant to be used as a flow control for running one of two queries. For that, use IF.

Second, you can't separate a CTE and a select because they're both part of the same statement. You can't just say, "Here's a CTE, run one of these two statements from it," because that's not how CTE statements are constructed. You can reuse a CTE in a select several times, but it will execute the entire select, not just part of it.

WITH CTE AS
(
    SELECT 
       ID, NAME, DEPT,
       CASE WHEN Fixed = 1 THEN 'Yes' ELSE '' END AS [Expr1],   
       CASE WHEN NotFixed = 1 THEN 'Yes' ELSE '' END AS [Expr2],
    . . .
)

To make establishing a view or table, selecting data, or inserting data easier, use a common table expression (CTE).

make use of a CTE As part of the view's SELECT query, CREATE a view.

Example 1: CTE statement create table sql server:

WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,
        JobTitle
 FROM   HumanResources.Employee)
SELECT EmployeeNumber,
       Title
FROM   Employee_CTE

Example 2: Create a table using a CTE by selecting another table with the CREATE TABLE AS SELECT (CTAS) clause.

CREATE TABLE s2 AS WITH q1 AS (SELECT key FROM src WHERE key = '4') SELECT * FROM q1;

It's just as simple to delete data from a CTE. Use CTE and a DELETE statement to accomplish this. The same CTE query definition is being used.

Example 1: records with a single duplicate column are deleted by sql server:

WITH cte AS (
    SELECT 
        contact_id, 
        first_name, 
        last_name, 
        email, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name, 
                last_name, 
                email
            ORDER BY 
                first_name, 
                last_name, 
                email
        ) row_num
     FROM 
        sales.contacts
)
DELETE FROM cte
WHERE row_num > 1;

Example 2: let’s delete the currency we created:

WITH CurrencyCTE AS
(
    SELECT
        CurrencyCode,
        Name,
        ModifiedDate
    FROM Sales.Currency
    WHERE CurrencyCode = 'TST'
)
 
DELETE
FROM CurrencyCTE
WHERE CurrencyCode = 'TST'

Example 3: delete duplicate rows in a table from a double-import for a school assignment. here's the query i wrote:

with cte_deleteduplicate as (
  select *, row_number() over (
   partition by ItemWeight, TotalCharge 
   order by ItemWeight, TotalCharge 
   ) as row_num
   from Cubed_Storage
   where Type in ('Small', 'Large')
   and YRMO in ('201810', '201811') 
   )
delete from cte_deleteduplicate
where row_num > 1;

Example 4: Joining CTE result with #SysObjects table and deleting rows having object_ids as odd numbers. Querying SELECT * FROM #SysObjects shows rows have been deleted.

--DELETE Statement With CTE 

WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT name, [object_id], create_date FROM SYS.objects
)
DELETE #SysObjects 
FROM #SysObjects as S INNER JOIN SysObjectsCTE as CTE ON S.ObjectId = CTE.ObjectId
WHERE  S.ObjectId % 2 = 1; 

SELECT * FROM #SysObjects;

A CTE cannot be used within an EXISTS function.

Example : However, the answer is straightforward: simply use a derived table instead. We used to utilize derived tables instead of CTEs before CTEs.

IF EXISTS(
    WITH LargeDepartmentNewHires AS (
        SELECT
              HREDH.DepartmentID
        FROM
            HumanResources.EmployeeDepartmentHistory HREDH
        WHERE
            HREDH.StartDate BETWEEN '1/1/1999' AND '12/31/1999'
        GROUP BY
              HREDH.DepartmentID
             , YEAR(HREDH.StartDate)
        HAVING
              COUNT(*) >=10
        )
    SELECT
          HRE.*
    FROM
        HumanResources.Employee HRE
            INNER JOIN
        HumanResources.EmployeeDepartmentHistory HREDH
            ON HRE.EmployeeID = HREDH.EmployeeID
            INNER JOIN
        LargeDepartmentNewHires LDNH
            ON HREDH.DepartmentID = LDNH.DepartmentID
    )
    BEGIN
        PRINT 'Yes it exists'
    END
ELSE
    BEGIN
        PRINT 'Does NOT exists'
    END

What is a Common Table Expression?

In SQL Server, a (non-recursive) CTE is regarded similarly to other constructs that can be used as inline-table expressions. While BOL claims that a CTE "may be conceived of as a temporary result set," this is merely a logical statement.

• A CTE can be used to recurse or just to increase readability.
• A valued function, like a view or inline-table, can be handled as a macro and enlarged in the main query.
• CTEs, like views, are integrated into the query rather than materialized and saved.

Advantages of Temp table over CTE:

CTEs can only be referenced by the current query for which they were generated. Once a temp table is built, it can be accessed by numerous queries in the same procedure.

What is a temporary table?

• This is a group of rows in tempdb that are saved on data pages. The data pages could be stored in memory in part or altogether.
• A temp table can be reused or used to execute numerous processing passes on the same data set.
• Another table with some rules about scope is a temp table.
• Temp tables are similar to regular tables, except that they are produced in the TempDB database.
• Just like a regular table, they have primary keys, constraints, and indexes.

Advantages of CTE over Temp table:

• CTE does not require any physical space, however, temp tables require space in the temp DB database.
• The CTE is quicker than the temp table.

A string parameter is passed along a stored process. The argument is utilized in the Select within the CTE's where section. Depending on the value passed down from the parameter, I'd like to be able to complete the choice with or without the where portion.

Example 1: CTE in the IF. It worked for me.

IF @awsome = 1
BEGIN
;WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
    SELECT 'WHATEVERYOUWANT' FROM CTE
END
ELSE IF @awesome = 2
BEGIN
;WITH CTE2
AS
( 
    SELECT * FROM SOMETABLE
)
    SELECT 'WHATEVERYOUWANT' FROM CTE2
END

Example 2: using CTE's Unless I'm incorrect, the phrase following the CTE appears to be an If statement, hence the CTE will not work. The following purely fictitious example, for example, will not even compile:

CREATE PROCEDURE dbo.cte_test( @CustomerNumber Int)
AS
BEGIN
With MyCTe
As

(Select *
From Customers
Where CustomerNumber = @CustomerNumber)
if (@CustomerNumber = 100)

Begin
Select * from Customers
End
END

Example 3:

If the @parameter = '*' run the select without the Where Part

If the @parameter !='*' runt the select with the where Part (Field = @parameter)

This is what I like to do I am not sure how to write it properly

With Cte as
(
      if (@parameter = '*')
      Begin
                  Select field1, field2, field3 from table1
      End
      Else
      Begin
                  Select field1, field2, field3 from table1
                   where field1 = @parameter
      End
)
Select * From cte
)

Common table expressions (CTE) queries are divided into two parts: the CTE and the SQL that references the CTE.

Example 1: I was able to run the select successfully, but when I tried to add an INSERT INTO above it, it refused to operate. Then it dawned on me that INSERT INTO is part of the primary query, therefore the WITH statement must come before everything. Here’s an example:

WITH ActiveUsers AS
(
    SELECT
        Users.UserName,
        Users.TimezoneID,
        ROW_NUMBER() OVER (
            PARTITION BY
                Users.TimezoneID
            ORDER BY
                Users.CreatedDate ASC) as UserRowNumber
    FROM
        Users
    WHERE
        (Users.IsDeleted = 0))
)   
INSERT INTO [#ProcessUsers]
    (UserID, TimezoneID)
    SELECT TOP (500) WITH TIES
        UserName
    FROM
        ActiveUsers
    WHERE
        UserRowNumber <= 100
    ORDER BY
        UserID;

Example 2: In a leading WITH clause, enter the results of a common table expression (CTE). Since the previous instructions lacked an example, I thought it would be a useful addition.

Create the message table, like this:

CREATE TABLE message
( message_id    SERIAL
, message_text  VARCHAR );
Now, here’s a CTE with a two fabricated rows:

WITH cte AS
( SELECT 'x-ray' AS msg
  UNION ALL
  SELECT 'MRI' AS msg )
INSERT INTO message
( message_text )
( SELECT msg FROM cte );

Then, you can query the results from the message table, like this:

SELECT * FROM message;

Output:

Unfortunately, if you run it again, it would duplicate the rows. Use correlation between the subquery in the WITH clause and the target table in the INSERT statement to prevent this occurrence.

Example 3: Using an insert or update statement inside the CTE is incorrect, according to the documentation, but you can use an insert or update statement outside of the CTE.

DECLARE @NumTableVar TABLE( n INT);
;WITH numbers (n)
AS (SELECT 1
UNION ALL
SELECT 1 + n
FROM   numbers
WHERE  n < 1000)

INSERT INTO @NumTableVar   (n)
SELECT n
FROM   numbers
OPTION (MAXRECURSION 0);

Example 4: The SysObjectsCTE, CTE is used. This CTE has a SELECT statement in the query specification, and it refers to a metadata table with column names supplied. In addition, the CTE result was inserted into the #SysObjects temp table.

--INSERT Statement With CTE
WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT TOP 100 name, object_id, create_date FROM SYS.objects
)
SELECT 1; --In Between Statement Breaks the Scope of CTE
INSERT INTO #SysObjects 
(
Name, 
objectid, 
createdDate
)
SELECT Name, ObjectId, CreateDate
FROM   SysObjectsCTE; --CTE

The issue is that after the CTEs, you have two queries. Only one. The queries are linked to the CTE. Simply add another CTE:

with query1 as (
      select myid, col1, mydate
      from sourcetable
     ),
     query2 as (
      select max(myid) as id
      from query1
     ),
     i as (
      insert into mytable   -- You should really list the columns here
          select co1, mydate
          from query1
     )
update anothertable
    set value = (select myid from query2)
    where col2 = 'avalue';

CTE syntax is constructed before the SELECT statement, allowing you to write queries with JOIN. SQL Server will instead construct a result set first and use the same resultset to join the SELECT statement after the CTE.

Note: Only INNER JOIN is used in the instance, and the conclusion only pertains to INNER JOIN. There is a completely different story in the case of OUTER JOIN, which we shall discuss in future blog entries.

Example 1: The Employees table generated in the Common Table Expression is used in this example (a.k.a CTE). If you haven't done so by now, go to the link to create the table.

WITH FirstCTE AS
   (SELECT Id EmployeeId, Name, ManagerId 
    FROM dbo.Employees WITH(NOLOCK) 
    WHERE ManagerId is NULL)
,   SecondCTE AS
    (SELECT E.Id EmployeeId, E.Name, E.ManagerId, 
            FCTE.Name ManagerName
     FROM dbo.Employees E WITH(NOLOCK)
        INNER JOIN FirstCTE FCTE
         ON E.ManagerId = FCTE.EmployeeId)

SELECT * FROM SecondCTE

Example 2: The AdventureWorks database is used in the query below. You can get the AdventureWorks2017 database if you don't already have it. It constructs a VendorCTE Common Table Expression and then joins it to the Vendor table. This CTE is handy for calculating the total due for VendorIDs.

WITH VendorCTE AS
   (SELECT  VendorID, SUM(TotalDue) AS TotalDue 
    FROM Purchasing.PurchaseOrderHeader PH
      JOIN Purchasing.PurchaseOrderDetail PD
       ON PH.PurchaseOrderID  = PD.PurchaseOrderID	        
       GROUP BY VendorID)
            
   SELECT	V.AccountNumber, V.Name, VC.SumTotalDue  
   FROM VendorCTE VC
   JOIN Purchasing.Vendor V
	   ON V.BusinessEntityID = VC.VendorID
   ORDER BY TotalDue DESC

Example 3: You can use JOIN in your queries because of the nested CTE declarations:

SELECT val1, val2, ...
FROM 
 (  SELECT top 1 id as val1
    FROM TableA
    ) A
CROSS JOIN 
 (  SELECT top 1 id as val2
    FROM TableB
     ) B
CROSS JOIN 
 (
 WITH r AS (     -- nested CTE definition
 SELECT * FROM film WHERE length = 120
 )
 SELECT CASE WHEN @flag = 'Y' THEN (
  SELECT * FROM r
  UNION ALL
  SELECT * FROM film
  WHERE length = 130
  AND NOT EXISTS (
  SELECT * FROM r
  )
  ) ELSE NULL END val3
) C

Example 4:

USE AdventureWorks2012
GO
WITH MyCTE AS
(SELECT c.CustomerID
FROM Sales.PersonCreditCard pc
INNER JOIN Sales.Customer c ON c.CustomerID = pc.BusinessEntityID)
SELECT cte.CustomerID, p.BusinessEntityID
FROM Person.Person p
INNER JOIN Person.EmailAddress ea ON ea.BusinessEntityID = p.BusinessEntityID
INNER JOIN MyCTE cte ON cte.CustomerID = p.BusinessEntityID
INNER JOIN Person.PersonPhone ph ON ph.BusinessEntityID = p.BusinessEntityID;

Limitations of CTE

• Though employing CTE has its benefits, it does have some drawbacks to consider. We already know that it may be used in place of a view, however, CTEs cannot be nested, whereas Views can.
• Views can be used as many times as they are defined, but CTE cannot. Every time you wish to use it, you should define it. CTE is not recommended in this circumstance since declaring batches repeatedly is a tedious task for the user.
• Operators such as UNION, UNION ALL, and EXCEPT should be used between the anchor members.
• You can declare multiple Anchor Members and Recursive Members in Recursive CTEs, but you must declare all of the Anchor Members before the first Recursive Member. Between two recursive members, you can't define an Anchor Member.
• The Anchor and Recursive Members should have the same number of columns and data types.
• Aggregate functions such as TOP, operators such as DISTINCT, clauses such as HAVING and GROUP BY, Sub-queries, and joins such as Left Outer, Right Outer, or Full Outer are not permitted in Recursive Member. Only Inner Joins are permitted in Recursive Member when it comes to Joins.
• The recursion limit is 32767, and crossing it causes the server to collapse due to an infinite loop.

Some disadvantages or cons using CTE

• Because a CTE is only a shorthand for a query or subquery, it can't be reused in another query like a view or function.
• In the event of frequently used queries, there may be a performance issue (If CTE is used in that query).
• CTE members are unable to utilize the keyword clauses Distinct, Group By, Having, Top, Joins, which restricts the types of queries that may be generated and reduces their complexity.
• In stored procedures, table variables and CTEs cannot be provided as parameters.

Select, insert, update, and delete statements that come after the CTE expression are the only ones that reference it. You can also use many tables to build a Common Table Expression.

The WITH clause is used in the CTE query to define an alias for a specified subquery. As an output, we can use the subquery in another WITH subquery or in the outer query that generates the final result set.

Example 1: Assume we've established a table called EMPLOYEE and filled it with data as seen below:

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);
Query OK, 0 rows affected (0.36 sec)

INSERT INTO Employee VALUES
   (101, 'Ramya', 'Rama Priya', 27, 'F', 9000, 101),
   (102, 'Vinay', 'Bhattacharya', 20, 'M', 6000, 102);
And, if we have created another table and populated it as −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
   (101, 'ramya@mymail.com', 'Hyderabad'),
   (102, 'vinay@mymail.com', 'Vishakhapatnam');
Following query create a Common Table Expression from the above two tables −

WITH
   exp1 AS (SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE),
   exp2 AS (SELECT EMAIL, PHONE FROM CONTACT)

SELECT * FROM exp1 JOIN exp2;

Result:

+-----+------------+--------------+------------------+-------+
| ID  | FIRST_NAME | LAST_NAME    | EMAIL            | PHONE |
+-----+------------+--------------+------------------+-------+
| 101 | Ramya      | Rama Priya   | ramya@mymail.com | NULL  |
| 102 | Vinay      | Bhattacharya | ramya@mymail.com | NULL  |
| 101 | Ramya      | Rama Priya   | vinay@mymail.com | NULL  |
| 102 | Vinay      | Bhattacharya | vinay@mymail.com | NULL  |
+-----+------------+--------------+------------------+-------+

Example 2: Our CTE query looks as follows:

WITH
p_pc AS (
    SELECT
        p.id AS post_id,
        p.title AS post_title,
        pc.id AS comment_id,
        pc.review AS comment_review,
        COUNT(post_id) OVER(PARTITION BY post_id) AS comment_count
    FROM post p
    LEFT JOIN post_comment pc ON p.id = pc.post_id
    WHERE p.title LIKE 'SQL%'
),

p_pc_r AS (
    SELECT
        post_id,
        post_title,
        comment_id,
        comment_review,
        DENSE_RANK() OVER (ORDER BY p_pc.comment_count DESC) AS ranking
    FROM p_pc
)

SELECT *
FROM p_pc_r
WHERE p_pc_r.ranking <= 2
ORDER BY post_id, comment_id

The JOIN and the WHERE clause that filters the post records are defined by the first subquery, p_pc. A COUNT Window Function is included in the p_pc query projection, which returns the number of comments for each post.

The second subquery is p_pc_r, which, as you've seen, refers to the p pc subquery we generated before. The p_pc_r subquery's purpose is to create a new column that ranks posts according to the number of comments they have. The DENSE RANK Window Function was utilized for this purpose.

The final query refines the p_pc_r query result set, extracting only the top two most-commented posts and their related comments.

Example 3: In this with clause, you can create multiple CTE tables.

with CTEtable(deptname,empcount)  
    as  
    (  
    select d.deptname as deptname, COUNT(e.id) as empcount from tbldept as d  
    join tblEmp as e on d.deptid=e.deptid  
    group by d.deptname  
    ),  
    tblnew_hr(deptname,id)  
    as  
    (  
    select d.deptname,e.id from tblEmp e join tbldept d on  
    e.deptid=d.deptid  
    )  
    select * from CTEtable  
    union   
    select * from tblnew_hr

All of the nested CTEs in a nested CTE query follows the same principle. Any CTE query can reach CTEs defined before it, but not ones defined after it.

CTEs nested together remind me of Russian nesting dolls. Consider the first CTE in a query with several CTEs to be the smallest doll.

That first CTE can't respond to any other CTEs, just as the smallest doll can't hold any additional dolls.

Think of the second CTE in a multiple CTE query as the second smallest doll in the Russian dolls. It can only have one doll fit inside of it. CTEs are similar in that the only CTE that can be referenced by the second CTE in a multiple CTE query is the first CTE.

The third CTE resembles a tiny Russian doll. Only the smallest or second smallest doll can be placed within the third smallest doll. It can only use the first or second CTE defined with the third CTE, not any further CTEs.

Nested CTEs

Consider the following example of a multiple CTE query with four nested CTEs: a T-SQL query is being used instead

;WITH CTE1 AS
 ( .... ),
 
CTE2 AS
 ( .... ),
 
CTE3 AS
 ( .... ),
 
CTE4 AS
 ( .... )
 
SELECT *
 FROM CTE4;

Example 1: Nested CTEs is a scenario where one CTE references another CTE in it.

WITH FirstCTE 
   AS (SELECT 1 EmployeeId, 'Shreeganesh Biradar' Name)
, SecondCTE 
   AS (SELECT EmployeeId, Name, 'India' Country FROM FirstCTE)
SELECT *   FROM SecondCTE

The Employees table, which was established in the previous article Introduction to Common Table Expressions, is used in this example (a.k.a CTE). If you haven't done so already, go to the link and build the table.

WITH FirstCTE AS
   (SELECT Id EmployeeId, Name, ManagerId 
    FROM dbo.Employees WITH(NOLOCK) 
    WHERE ManagerId is NULL)
,   SecondCTE AS
    (SELECT E.Id EmployeeId, E.Name, E.ManagerId, 
            FCTE.Name ManagerName
     FROM dbo.Employees E WITH(NOLOCK)
        INNER JOIN FirstCTE FCTE
         ON E.ManagerId = FCTE.EmployeeId)

SELECT * FROM SecondCTE

Example 2: Nested CTEs Sample Code

;WITH CustomersWithMostOrdersCTE AS
-- First CTE
(
  SELECT TOP 10 CustomerID,
                count(1) as numOrders
    FROM SalesInvoice
   GROUP BY CustomerID
   ORDER BY count(1) DESC
),

-- Second CTE
CustomersOrdersWithNamesCTE AS
(
  SELECT cust.FirstName, cust.LastName,
         cte.*
    FROM Customer AS cust
   INNER JOIN CustomersWithMostOrdersCTE AS cte
           ON cte.CustomerID = cust.CustomerID
)

SELECT *
  FROM CustomersOrdersWithNamesCTE;

The data within CTE is ordered. Ordering data within CTE is impossible due to its nature.

Temporary tables and table variables were no longer required in scripts. Improve the readability of code.

Only the final query at the bottom of the CTE affects all tables in multiple CTEs, resulting in superior resource utilisation.

If you want deterministic behaviour, use SELECT TOP n Otherwise, the server can return whatever 10 rows it wants. You are witnessing totally normal behavior.

Example 1: This is not allowed:

WITH Sample_CTE (ID)
AS (
     SELECT Col14 AS ID
     FROM SomeTable AS C
     INNER JOIN OtherTable AS S
          ON C.LocationID = S.LocationID
     ORDER BY C.PKID ASC
)
SELECT *
FROM Sample_CTE
GO

Output: Error Message:

Example 2: To solve the problem, specify an ORDER BY inside the CTE:

WITH results AS
(
    SELECT TOP 10 ID, Date
    FROM dbo.items
    ORDER BY ID DESC 
)
SELECT ID
FROM results
ORDER BY Date

Example 3: WITH CTE and smallest and largest orders by month in 2013.

WITH Cte (Month, Min, Max) AS (
  SELECT MONTH(OrderDate) AS Month,
         MIN(TotalAmount) AS Min,
         MAX(TotalAmount) AS Max
    FROM [Order]
   WHERE YEAR(OrderDate) = 2013
   GROUP BY MONTH(OrderDate)
)

SELECT Month, 
       Min AS MinOrder,
       Max AS MaxOrder
  FROM Cte
 ORDER BY Month

Output:

MONTH	MINORDER	MAXORDER
1   	    49.80		11493.20
2	    174.90		5793.10
3	    147.00		10495.60
4	    136.80		10588.50
5	    110.00		10191.70

The recordset is filtered out using SQL Server Common Table Expression (CTE) and PARTITION BY. PARTITION is similar to GROUP BY in most cases.

A temporary resultset in SQL Server is a CTE. The scope is for the query and it is not saved as an object.

Example 1: Recursive CTE is not necessary. You can just use a PARTITION BY method.

SELECT tid
  , AMT
  , SUM(amt) OVER(PARTITION BY tid ORDER BY tid 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     ) AS RunningTotal
FROM #Transactions

update Sorry. I just finished reading the comments. If you must use CTE, follow @Squirrel's advice. You can use the aforementioned method if you are familiar with window functions.

Example 2: "I'm constructing a new universe that will house transaction data, and there will be sequential records that will hold the modifications for the key element." For example

Element	               Date              Sequence         
A                      25/01/2011        1                     
A                      26/01/2011        2
A                      29/01/2011        3
A                      02/02/2011        4
B                      01/01/2011        1
B                      07/02/2011        2

I'm trying to set up a scenario in which we can generate a report that shows the most recent data for each element on a certain date.

So, if you filter on 31/01/2011, the report will only show the most recent Element on or before that date.

A                      29/01/2011        3
B                      01/01/2011        1

This is the response in abbreviated form :

SET DATEFORMAT dmy

GO
CREATE TABLE #test
(element VARCHAR(1),
date_entry datetime,
sequence INT
)
GO

INSERT INTO #test

SELECT 'A','25/01/2011',1
UNION
SELECT 'A','26/01/2011',2
UNION
SELECT 'A','29/01/2011',3
UNION
SELECT 'A','02/02/2011',4
UNION
SELECT 'B','01/01/2011',1
UNION
SELECT 'B','07/02/2011',2
GO

WITH element_CTE (element,date_entry,sequence,ranking)

AS
(select element,date_entry,sequence, Rank() over
 (PARTITION BY Element order by Sequence DESC) as Rank

from #test 

WHERE date_entry < '31/01/2011'
)
select * from element_CTE  where ranking = 1

GO

Example 3: Employees table:

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
|  1   | Mark      | Hastings | Male   | 60000  |
|  1   | Mark      | Hastings | Male   | 60000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
+------+-----------+----------+--------+--------+

CTE (Common Table Expression) :

WITH EmployeesCTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
   FROM Employees
)
DELETE FROM EmployeesCTE WHERE RowNumber > 1

Output:

|  ID  | FirstName | LastName | Gender | Salary |
+------+-----------+----------+--------+--------+
|  1   | Mark      | Hastings | Male   | 60000  |
|  2   | Mary      | Lambeth  | Female | 30000  |
|  3   | Ben       | Hoskins  | Male   | 70000  |
+------+-----------+----------+--------+--------+

Example 4: SQL with CTE and over partition by:

create table #demo (id int, race int, lap int)

insert into #demo values (1,21,11),
(2,21,null),(3,21,null),(4,21,null),
(5,29,65),(6,29,null),(7,29,null),(8,29,null);

with CTE as
(select race, ROW_NUMBER() over (partition by race  order by race) "extra_lap" from #demo where lap is null),
CTE2 as 
(select race, lap "lap" from #demo where lap is not null)
select race, lap from CTE2
union 
select CTE.race, CTE2.lap + CTE.extra_lap "lap" from CTE join CTE2 on CTE.race=CTE2.race

drop table #demo;

To pivot the data, we'll need to establish a temporary data collection that we can pivot. To accomplish this, you can utilise CTE, Derived Tables, or Temporary Tables.

Example 1: The SQL script includes DDL to establish a table and DML to insert a few rows of test data, followed by a CTE that performs aggregations and uses the Pivot operator to display the data by year and month, as well as a new yearly total amount.

CREATE TABLE orders 
 ( order_id  int identity(1,1) primary key
 , amount    numeric(10,2)
 , order_dt  datetime
) ;

INSERT orders 
( amount, order_dt ) VALUES
 ( 10.01, '4-01-2019' )
,(  9.99, '3-16-2019' )
,(  7.01, '1-15-2019' )
,( 23.46, '4-15-2019' )
,(  3.16, '2-14-2019' )
,(  4.13, '2-11-2019' )
,( 12.14, '7-04-2019' )
,( 13.16, '5-20-2019' )
,(  6.10, '8-23-2019' )
,(  1.53, '1-01-2020' )
,( 40.66, '1-18-2020' )
;

;with cte_amt as
( select [amount], year(order_dt) as [yr], yr_amount, month(order_dt) as [mo]
  from [orders] o
  JOIN ( select year([order_dt]) as [yr], sum([amount]) as [yr_amount] from [orders] group by year([order_dt]) ) as y 
    ON year([order_dt]) = y.[yr]
)

select yr as [Year], [yr_amount] as [Annual Total]
  , coalesce([1] ,0) as  [Jan], coalesce([2] ,0) as  [Feb], coalesce([3] ,0) as  [Mar]
  , coalesce([4] ,0) as  [Apr], coalesce([5] ,0) as  [May], coalesce([6] ,0) as  [Jun]
  , coalesce([7] ,0) as  [Jul], coalesce([8] ,0) as  [Aug], coalesce([9] ,0) as  [Sep]
  , coalesce([10],0) as  [Oct], coalesce([11] ,0) as [Nov], coalesce([12],0) as  [Dec]
from cte_amt

pivot(sum(amount) for mo in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as pvt;

Example 2: pivot and cte used in sql:

-- cte here as above
SELECT  Description
 ,Objectives
 ,Specification
 ,Requirements
FROM cte_1 PIVOT ( max(Words) FOR ReferenceName IN ( Description,
    Objectives,
    Specification,
    Requirements ) ) AS PivotTable

Use a WITH clause with one or more comma-separated subclauses to indicate common table expressions. Each subclause specifies a subquery that generates a result set and assigns a name to it.

Syntax:

WITH name (column1, column2, ..., columnN) AS (
  SELECT statement
)

• name -- the name of the CTE to which a subsequent query refers.
• column1, ... -- column names representing the columns in the SELECT statement.
• SELECT statement -- a query used to populate the CTE result set.

Example 1: List customers in the US and their order counts and order amounts for 2013.

WITH CteOrder (CustomerId, OrderCount, OrderAmount) AS (
  SELECT CustomerId,
         COUNT(Id),
         SUM(TotalAmount)
    FROM [Order]
   WHERE YEAR(OrderDate) = 2013
   GROUP BY CustomerId
),
CteCustomer (Id, FirstName, LastName) AS (
  SELECT Id, FirstName, LastName
    FROM Customer
   WHERE Country = 'USA'
)

SELECT FirstName, LastName, OrderCount, OrderAmount
  FROM CteOrder O
  JOIN CteCustomer C ON O.CustomerId = C.Id
 ORDER BY LastName

Output:

FIRSTNAME	LASTNAME	    ORDERCOUNT	ORDERAMOUNT
Art		    Braunschweiger	2   		2972.50
Karl		Jablonski	    8	    	10262.55
Yoshi		Latimer		    3	    	2283.20
Helvetius	Nagy		    2	    	1333.30
Liz		    Nixon		    2	    	2955.40

Example 2: A SELECT query accompanied by a WITH clause that contains a CTE named cteReports is constructed for the Employee table:

WITH   
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel  
AS  
(  
Select EmployeeID, FirstName, LastName, ManagerID, 1  
FROM Employees  
//WHERE ManagerID IS NULL  
UNION ALL  
SELECT e.StudentID, e.FirstName, e.LastName, e.ManagerID,  
r. StuLevel + 1  
FROM Students p  
INNER JOIN cteReports s  
ON e.ManagerID = r.StuID  
)  
SELECT  
First Name+ ' ' + LAST NAME AS FullName,StuLevel,  
(SELECT FirstName + '.....' +LastName FROM Students  
WHERE StudentID = cteReports.MgrID) AS ManagerFROM cteReports  
ORDER BY StuLevel, MgrID  

Example 3: The WITH clause in the given example specifies the CTEs cte1 and cte2, and the top-level SELECT that follows the WITH clause refers to them:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

When using a nested statement with several subqueries in the FROM clause, your query will most probably expand large, complex, and difficult to comprehend.

Example 1: CTE in subquery

SELECT max(c) FROM (
    WITH t(c) AS (SELECT 1)
    SELECT * FROM t
);

Output:

CTE in subquery expression

SELECT (
    WITH t AS (SELECT 1)
    SELECT * FROM t
);

Output:

Example 2: Because many queries are written with the goal of being saved and rerun later, effective organization is essential for a project to succeed. Subqueries should be grouped as CTEs to reduce time, space, and confusion.

-- Set up your CTE
___ match_list ___ (
    SELECT 
  		country_id,
  	   (home_goal + away_goal) AS goals
    FROM match
  	-- Create a list of match IDs to filter data in the CTE
    WHERE id IN (
       SELECT ___
       FROM ___
       WHERE season = ___ AND EXTRACT(MONTH FROM ___) = ___))

-- Select the league name and average of goals in the CTE
SELECT 
	___,
    ___(___)
FROM league AS l
-- Join the CTE onto the league table
LEFT JOIN ___ ON l.id = match_list.___
GROUP BY l.name;

Example 3: CTE on top and access it in the subquery

WITH YourCTE(blubb) AS
(
    SELECT 'Blubb'
)
SELECT id,
       (SELECT blubb FROM YourCTE),
       name
FROM   table_name

Example 4: CTEs may make code flow and read lot simpler when writing out intricate queries, which is actually the root of the issue.

WITH deps as (
            SELECT department_id, COUNT(people) as department_count
            FROM department as d
            WHERE department_type = 'sales'
            GROUP BY department_id
),
sales as (
            SELECT person_id, SUM(sales) as total_sales
            FROM orders
            WHERE orders.department_id = d.department_id
            GROUP BY person_id
)

SELECT p.first_name, p.last_name, d.department_count, s.total_sales
FROM persons as p
INNER JOIN deps as d ON d.department_id = p.department_id
LEFT OUTER JOIN sales as s ON s.person_id = p.person_id

In this contrived instance, the difference is probably not as evident, but once you have three or four levels of subqueries, SQL's readability, maintainability, and extensibility skyrocket.

The only issue I would mention is that you must include the ';' statement terminator when using a CTE within a transaction.

Syntax:

BEGIN TRANSACTION
;WITH CTE .....
SELECT.....etc.
COMMIT TRANSACTION

Example: All the changes that have an impact on the table that we're interested in. But wait a minute, there appear to be a couple of critical bits of information lacking, namely the transaction's Start Time and Transaction SID.

Those pieces of information aren't recorded with the individual operations for whatever reason that is beyond me but that I'm sure has a very good purpose, but they are recorded somewhere, and that is during the beginning and commit phases of a transaction. Now that we have the transaction IDs, it shouldn't be too difficult to discover the associated beginning transaction lines, and thus the SID of the logon that conducted the operations, with a little more modification to our code.

WITH CTE AS
(SELECT Operation, Context, [Transaction ID], OBJECT_NAME(object_id) ObjectName, [Begin Time], [Transaction SID]
FROM fn_dblog(NULL,NULL)
JOIN sys.partitions ON fn_dblog.PartitionId = partitions.partition_id
WHERE OBJECT_NAME(object_id) = 'person'
AND index_id = 1)
 
SELECT CTE.Operation, CTE.Context, CTE.[Transaction ID], CTE.ObjectName, 
fn_dblog.[Begin Time], fn_dblog.[Transaction SID], SUSER_SNAME(fn_dblog.[Transaction SID]) AS UserName
FROM CTE
JOIN fn_dblog(NULL,NULL) ON CTE.[Transaction ID] = fn_dblog.[Transaction ID]
WHERE fn_dblog.Operation = 'LOP_BEGIN_XACT'

The tables use cte, and distinct cte's can combine several queries.

Syntax for Union Of Two CTEs, Follow Below Code Snippets :

WITH CTE_TableA
AS
(
SELECT * FROM TableA
),CTE_TableB
(
SELECT * FROM TableB
)

SELECT * FROM CTE_TableA
UNION ALL
SELECT * FROM CTE_TableB

Example 1: EmployeeDetails and CTE information are obtained using UNION ALL in this Common Table Expression Query.

WITH
  EmployeeCTE (EmpID, FirstName, LastName, ManagerID,Department, EmpLevel)
  AS
  (
 SELECT EmpID, EmpFirstName, EmpLastName, ManagerID, Department, 1
 FROM EmployeeDetails
 WHERE ManagerID IS NULL
 UNION ALL
 SELECT E.EmpID,E.EmpFirstName,E.EmpLastName,E.ManagerID, E.Department,
  C.EmpLevel + 1
  FROM EmployeeDetails E
  INNER JOIN EmployeeCTE C
  ON E.ManagerID = C.EmpID
  )
SELECT
  FirstName ,LastName,EmpLevel,Department,
  (SELECT EmpFirstName + ' ' + EmpLastName FROM EmployeeDetails
    WHERE EmpID = EmployeeCTE.ManagerID) AS Manager
FROM EmployeeCTE
ORDER BY EmpLevel, ManagerID

Example 2: CTE with UNION ALL:

WITH Results_CTE AS
(
    SELECT
        t2.SomeIntKey2 as Key,
        ROW_NUMBER() OVER (ORDER BY SomeIntKey2) AS RowNum  
    FROM
        Table2 t2
    LEFT JOIN CalculatedData d
        ON  d.Key = t1.SomeIntKey2
    WHERE Postcode LIKE 'CHX 1XX%' 
    UNION ALL 
    SELECT
        t1.SomeIntKey1 as Key,
        0 as RowNum
    FROM
        Table1 t1
    LEFT JOIN CalculatedData d
        ON  d.Key = t1.SomeIntKey1
    WHERE Postcode LIKE 'CHX 1XX%' 
 )
 SELECT * FROM Results_CTE a INNER JOIN CalclatedData d ON a.SomeIntKey2 = d.Key
 WHERE RowNum >= 0 AND RowNum <= 10 OPTION(RECOMPILE) 

Please note that the second part of the UNION ALL now always returns 0 for the RowNum.

Example 3: To show the results one by one as a recursive display, we use a Union All to increment RowNo, to add the day one by one till the criteria matched the date range, and to stop the recursion, we need to specify some condition. We repeat the recursion in this instance to display our records until the end date is less than or equal to the start date.

declare @startDate datetime,  
        @endDate datetime;  
  
select  @startDate = getdate(),  
        @endDate = getdate()+16;  
-- select @sDate StartDate,@eDate EndDate  
;with myCTE as  
   (  
      select 1 as ROWNO,@startDate StartDate,'W - '+convert(varchar(2),  
            DATEPART( wk, @startDate))+' / D ('+convert(varchar(2),@startDate,106)+')' as 'WeekNumber'       
  union all  
       select  ROWNO+1 ,dateadd(DAY, 1, StartDate) ,  
              'W - '+convert(varchar(2),DATEPART( wk, StartDate))+' / D ('+convert(varchar(2),  
               dateadd(DAY, 1, StartDate),106)+')' as 'WeekNumber'     
  FROM  myCTE  
  WHERE dateadd(DAY, 1, StartDate)<=  @endDate    
    )  
select ROWNO,Convert(varchar(10),StartDate,105)  as StartDate ,WeekNumber from myCTE 

Use standard table expressions to update data in a table, which becomes especially intuitive when using JOINS.

If your CTE is built on a single table, you can update it using CTE, which will update the underlying table as well.

Using a CTE to update your source data seems like a long shot. Normally, I prefer to change my source tables directly, however in some circumstances, updating your source data via a CTE may make your queries more understandable.

Example 1: little stumped with this CTE update stmt:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

WITH cte AS 
(
    SELECT * FROM @a
)
UPDATE cte
SET    Value = b.Value
FROM   cte AS a
INNER JOIN @b AS b 
ON     b.ID = a.ID

SELECT * FROM @a
GO

Example 2: The AdventureWorks2012 database was used. We're simply using a CTE to identify one unique employee: Angela. We're changing her vacation hours to include two extra hours because she worked some overtime:

WITH EmployeeCTE AS
(
    SELECT
        NationalIDNumber,
        LoginID,
        JobTitle,
        VacationHours
    FROM HumanResources.Employee
    WHERE NationalIDNumber = 563680513
)
 
 
UPDATE EmployeeCTE
SET VacationHours += 2

Example 3: I found this approach for updating the ranks of my table Performance, but I'm not sure why it works:

WITH cte AS (
    SELECT AllTimeRank, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds) 
    FROM dbo.Performance
)
UPDATE cte 
SET AllTimeRank = r FROM cte;

The statement says UPDATE CTE but in fact, it really updates the table (dbo.Performance).

Example 4: UPDATE Statement With CTE

UPDATE statement with CTE. Here, the same CTE definition is used, and the CTE result is joined to the #SysObjects temp table. The UPDATE statement adds 'Test' to every name in the #SysObjects Name column.

WITH SysObjectsCTE (Name, ObjectId, CreateDate) --CTE Definition
AS
(
SELECT name, object_id, create_date FROM SYS.objects
)
UPDATE #SysObjects
SET    Name = CONCAT('Test-',S.Name) 
FROM   #SysObjects as S INNER JOIN SysObjectsCTE as CTE ON CTE.ObjectId = S.ObjectId; --CTE

SELECT * FROM #SysObjects