CTE, Common Table Expressions can be considered to be a temporary table within a query (commonly used in a stored procedure or a function), one advantage of it is that it can refer to itself and thus makes recursive query possible.
example:
with SalesAmountByCustomer as
(
Select
CustomerID,
sum(SalesAmount) as SalesAmount
From
CustomerSales
Group By
CustomerID
)
Select
CustomerName,
SalesAmount
From
Customer
Join SalesAmountByCustomer on Customer.CustomerID = SalesAmountByCustomer.CustomerID
Order By
CustomerName
For those who understand the concept of recursion, there is always a base condition, and a parameter which the function takes and manipulate. While the base condition is not met, it will repeatedly call itself with the manipulated parameter and the function will keep manipulate the parameter until the base condition is met.
CTE acts the same way.
A CTE recursive query has the following structure:
with "CTE Name" as
(
"select statement that represent the first call"
Union ALL
"select statment which refers to the CTE, usually a join with the CTE itself"
)
The second part will eventually return no result (just like a recursive function will eventually have its base condition met).
Practical example:
Suppose we have an employee table that looks like this:
| EmployeeID | Username | Name | SuperiorID |
| 1 | x1234 | Professor X | null |
| 2 | s1234 | Storm | 1 |
| 3 | w1234 | Wolverine | 1 |
| 4 | c1234 | Cyclops | 3 |
And to find out Cyclops superior and his/her superior and so forth, one can write the following query:
with Sup as
(
SELECT EmployeeID, Name, SuperiorID FROM Employee WHERE name = 'Cyclops'
UNION ALL
SELECT E.EmployeeID, E.Name, E.SuperiorID FROM Employee E JOIN Sup on Sup.SuperiorID = E.EmployeeID
)
Select Name from Sup
Another thing I did with recursive CTE was to generate a "Heirarchy" view for a dropdown list in my SSRS report.
Using the above table as example, the query would then become like this:
with list as
(
Select
cast(Name as varchar(max)) as Heirarchy,
'-' as Spacing,
EmployeeID,
Name,
SuperiorID
From
Employee
Where
Name = 'Professor X'
Union All
Select
cast(Parent.Heirarchy + Child.Name as varchar(max)) Heirarchy,
cast(' '+Parent.Spacing as varchar(max)) Spacing,
Child.EmployeeID,
Child.Name,
Child.SuperiorID
From
List as Parent
Join Employee as Child on Child.SuperiorID = Parent.EmployeeID
)
Select
Spacing + Name EmployeeName,
From
List
Order By
Heirarchy
The above code will produce the following result:
-Professor X
-Storm
-Wolverine
-Cyclops
