Sunday, September 11, 2011

Recursive query with CTE

This is my first time writing a technical blogpost. Any comments are welcome.

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:

EmployeeIDUsernameNameSuperiorID
1x1234Professor Xnull
2s1234Storm1
3w1234Wolverine1
4c1234Cyclops3

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