A Little Background: Common Table Expressions
I tend to obsess about the format of SQL expressions. Without broadly adopted norms for indentation, capitalization, etc., complex SQL expressions tend to be difficult to read. Making code pretty has little to do with the addition of Common Table Expressions (CTEs) to SQL Server 2005's feature list, except that, like careful formatting, CTEs can help make difficult-to-read queries less confusing.
Simply stated, CTEs possess the behaviors of views, letting you write "views" that exist only for the duration of the query. But they also offer many other capabilities, including the ability to recursively query tables. Most of their functionality is beyond the scope of this article. However, I've used them in this article to make my SQL expressions more readable and allow you to focus more specifically on the functionality of the PIVOT
As I stated, one common use of CTEs is to simplify SQL expressions. For example, the code below queries for products that have had at least one million dollars in product sales during 2004. The CTE, named ProductSales
, returns a list of product IDs, along with their sales volume by year. The SQL query shown below the CTE uses the ProductSales
CTE to query for products whose total sales for 2004 exceeded one million dollars. The expression also joins to the Product
table to add the name of the product.
|Simply stated, CTEs possess the behaviors of views, letting you write "views" that exist only for the duration of the query. |
--CTE used to simplify SQL expression
WITH ProductSales(ProductID, OrderYear, TotalSales)
JOIN sales.salesorderheader hdr
ON det.salesorderid = hdr.salesorderid
-- get list of products who had more than $1M
-- in sales in 2004
JOIN Production.Product prd ON sal.ProductID = prd.ProductID
TotalSales > 1000000
and OrderYear = 2004
If this example doesn't help to highlight the simplifications that you can achieve with CTEs, read onthe PIVOT
functionality should serve to help make this clear.