Common Table Expression (CTE) is MS SQL Server

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. 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. A CTE can be self-referencing and can be referenced multiple times in the same query unlike a derived table.

In Common Table Expression we can create a recursive query. Reference the resulting table multiple times in the same statement. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

EXAMPLE : 1

UPDATE or SELECT single row from a table which has many realted duplicates row

WITH CTE (config_element, rowNum)

AS

(

SELECT config_element,

ROW_NUMBER() OVER(PARTITION BY config_element ORDER BY config_element) AS rowNum

FROM [myDb].[dbo].[temp_configuration]

)

--UPDATE CTE SET config_element='factoryShortKeys' WHERE rowNum=2

SELECT * from CTE WHERE rowNum = 2

GO

EXAMPLE : 2

Using multiple CTE in single select query

WITH CTE_test1 AS (SELECT 100 AS Col1),

CTE_test2 AS (SELECT COL1+1 AS Col2 FROM CTE_test1)

SELECT CTE_test1.Col1, CTE_test2.Col2 FROM CTE_test1 

CROSS JOIN CTE_test2

GO

EXAMPLE : 3

Using multiple CTE in single select query

WITH CTE_test1  AS (SELECT 100 AS Col1),

CTE_test2 AS (SELECT COL1+1 AS Col2 FROM CTE_test1)

SELECT CTE_test1.Col1, CTE_test2.Col2 FROM CTE_test1 

CROSS JOIN CTE_test2

GO

For further detail see link:

http://blog.sqlauthority.com/2011/05/10/sql-server-common-table-expression-cte-and-few-observation/