Tip 2: CTEs and Recursive Queries (1 of 2)
Scenario: You have a hierarchy of product items, groups, and brands, all in a single table. Each row has a parent key that points to the row's parent. For any single row, you want to know all the row's parent rows, or all the row's child rows.
|Recursive queries are powerful—so powerful that you may not always see all their power at once. Next time you think you need to write code to loop through data, consider a CTE and a recursive query instead.|
SQL Server 2000 developers commonly requested the ability to more easily query against hierarchical data. Many databases store hierarchical data, such as databases for menu options, bill of materials, and geographic hierarchies. E-commerce applications in particular will often store a company's product hierarchy in a single table, where each row contains a pointer key to the row's parent. The table might look something like the contents of Table 1.
Table 1: The table shows a sample product hierarchy for an E-commerce application.
Brand A, Group 1
Brand A, Group 2
Brand B, Group 3
Brand A, Group 1, Item a
Brand B, Group 3, Item b
Imagine writing even a simple query to determine all the parent or child records from the table, especially when there is no limit to the number of levels. Fortunately, the common table expression and recursive query capabilities in SQL Server 2005 make this very easy. The key is to use a set of language constructs that allow you to first retrieve your main or "core" selection(s), and then a second set of queries or processes that takes the result of your core selections and repeatedly traverses (either up or down) a hierarchy to find subsequent matches.
populates a simple product hierarchy, and shows how to use recursive query capabilities to determine all the parent and child rows for a specific row.
A basic recursive query consists of three parts:
First, you must define a common table expression (CTE). Note the semicolon at the beginning of the statement:
; WITH ProductCTE AS
Second, you construct your main or core (or "anchor") query:
(SELECT ID, Name, ParentID FROM @tProducts
WHERE Name = @cSearch
So far, easy stuff. The major work occurs in the third step. You want to add a UNION ALL
statement and construct a query that references the CTE. Notice that the query does a JOIN
on the ProductCTE
based on a match between each ID
and the ParentID
. SQL Server 2005 will continue to repeat this query (hence the name "recursive" until it exhausts all searches):
-- Recursive query
SELECT Prod.ID, Prod.Name, Prod.ParentID
FROM @tProducts Prod
INNER JOIN ProductCTE ON ProductCTE.ID =
Now that you've seen a basic example of recursive queries, I'll move on to a second example that shows their power.
Tip 3: CTEs and Recursive Queries (1 of 2)
Scenario: You need to take a table of vendor orders and produce a flat result set for a weekly report or chart for the first quarter of 2004. The result set must contain one row for each vendor and week-ending date, with the sum of orders for that week (even if zero).
If you've ever written queries for weekly reports or weekly charts, you know that creating the full result set can be the trickiest part. Some reporting and charting systems require a matrix or even some form of a Cartesian product as the data source.
In the example above, you need to create a result set with one record per week per vendor account, even if the account did not have any orders for the week. You may be tempted to write a loop (either in T-SQL or in the application layer) to produce a table with the accounts and dates, and then join that against the orders themselves while searching for any orders between each week. However, you can also construct a recursive query to perform all the work in a stored procedure.
Before you write such a recursive query, you need to construct a function that will convert any date to an end of the week date. Orders may take place any day of the week, but you need to convert any date to a week-ending date (normally Saturday, as most businesses treat a business week as Sunday through Saturday. The following code is a SQL user-defined function (UDF) to convert any day to a Saturday date—so if you pass '6-13-2006'
as a parameter, you'll get '6-16-2006'
as the return value from the UDF:
ALTER FUNCTION [dbo].[GetEndOfWeek]
-- Converts date to the Saturday date for the week
-- So 6-12-07 becomes 6-16-07
RETURNS DateTime AS
DECLARE @dRetDate DateTime
SET @dRetDate =
@dDate + ( 7-DATEPART(WeekDay, @dDate))
Next, you need to construct a matrix of dates and vendors. The report calls for a weekly result set by vendor for the first quarter of 2004. Since you may not know the first Saturday in 2004 (nor the last Saturday), you can simply declare two variables for the '1-1-2004'
strings, and use the UDF to convert them to the actual Saturday dates:
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dbo.GetEndOfWeek('1-1-2004')
-- First Saturday
SET @EndDate = dbo.GetEndOfWeek('3-31-2004')
-- Last Saturday
After that, you'll next create two CTEs, one for a range of dates and a second for a list of vendors with at least one order. You can actually combine CTE declarations into a single large statement and delimit them with commas. The first CTE, DateCTE
, will contain a row for each Saturday date in the date range. Again here is where you may be accustomed to writing some kind of loop to create a list. But now you can use recursive queries. To do so, you declare your CTE and set your main query, which simply does a SELECT
of the single starting date:
; WITH DateCTE (WeekEnding) AS
(SELECT @StartDate AS WeekEnding
At this point, the DateCTE
has only one row in it, containing the '1-3-2004'
value. The recursive query will repeatedly query DateCTE
for values greater than 7 days beyond each entry, which are less than the ending date. So the recursive query will eventually build a row in DateCTE
, etc., all the way to the ending date:
SELECT WeekEnding + 7 AS WeekEnding
WHERE WeekEnding < @EndDate ),
Now that you've built the DateCTE
, you can build a simple CTE for each vendor with at least one order:
VendorList (VendorID) AS
(SELECT VendorID FROM
GROUP BY VendorID)
Finally, you can query against the two CTEs and the PO table to build the final result. Notice that the final query calls the GetEndOfWeek
UDF to convert each OrderDate
to a Saturday date, to match it up against the corresponding end of week date in DateCTE
. Listing 2
contains the complete source code for the CTEs and the final query, but here's the relevant portion:
SUM(COALESCE(Orders.TotalDue,0)) AS WeeklySales
FROM (SELECT VendorID, WeekEnding FROM
DateCTE, VendorList) AS Matrix
LEFT JOIN Purchasing.PurchaseOrderHeader Orders
ON dbo.GetEndOfWeek(Orders.OrderDate) = Matrix.WeekEnding
AND Orders.VendorID = Matrix.VendorID
GROUP BY Matrix.VendorID,Matrix.WeekEnding
Recursive queries are powerful—so powerful that you may not always see all their power at once. Next time you think you need to write code to loop through data, consider a CTE and a recursive query instead.