CTE (SQL SERVER)

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. 
This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.





Examples:

1. Creating a simple common table expression

-- Define the CTE expression name and column list.

WITH Person_CTE (PersonID, PersonName, PersonSalary)

AS
-- Define the CTE query.
(
Select 
 PersonID, PersonName, PersonSalary
FROM 
 Person
)

-- Define the outer query referencing the CTE name.

SELECT

 * 
FROM 
 Person_CTE
ORDER BY 
 PersonID;

2. Using a recursive common table expression to display multiple levels of recursion

WITH Emp(superiorid, id, username) AS

(
SELECT superiorid, id, username
FROM User 
WHERE superiorid IS NULL

UNION ALL

SELECT e.superiorid, e.id, e.username
FROM User AS e
INNER JOIN IS_User AS e1
ON e.superiorid = e1.id 
)

SELECT superiorid, id , username

FROM Emp
ORDER BY superiorid;