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.
with SalesAmountByCustomer as
sum(SalesAmount) as SalesAmount
Join SalesAmountByCustomer on Customer.CustomerID = SalesAmountByCustomer.CustomerID
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"
"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).
Suppose we have an employee table that looks like this:
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'
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
cast(Name as varchar(max)) as Heirarchy,
'-' as Spacing,
Name = 'Professor X'
cast(Parent.Heirarchy + Child.Name as varchar(max)) Heirarchy,
cast(' '+Parent.Spacing as varchar(max)) Spacing,
List as Parent
Join Employee as Child on Child.SuperiorID = Parent.EmployeeID
Spacing + Name EmployeeName,
The above code will produce the following result: