CTE or Common Table Expression, is a named temporary result set that we can define and reference within the execution scope of single SELECT, INSERT, UPDATE, DELETE, MERGE or CREATE VIEW statement.
A CTE is similar to a view, but it is defined within the execution of a single statement and lasts only for the duration of that statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.

Syntax of CTE in SQL Server-

WITH<cte_name>(<column1>..<columnN>) AS (<cte_definition>) <sql_statement>;

Example of CTE in SQL Server-

WITH <CTE_Name> AS (

   SELECT <column1, column2, …>

   FROM <table1>

   WHERE <condition1>

)

SELECT *

FROM <CTE_Name>

WHERE <condition2>;

Example for use of Multiple CTE –

WITH CTE_Name1 AS (

   SELECT column1, column2, …

   FROM table1

   WHERE condition1

), CTE_Name2 AS (

   SELECT column1, column2, …

   FROM table2

   WHERE condition2

)

SELECT *

FROM CTE_Name1, CTE_Name2

WHERE condition3;