Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl : Page 2

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 expression.

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)
   AS (
      SELECT 
         det.productID, 
         YEAR(hdr.orderdate), 
         SUM(det.linetotal) 
      FROM 
         sales.salesorderdetail det
         JOIN sales.salesorderheader hdr 
   ON det.salesorderid = hdr.salesorderid 
      GROUP BY 
         det.productID, 
         YEAR(hdr.orderdate)   
   )
   
   -- get list of products who had more than $1M 
   -- in sales in 2004
   SELECT 
      sal.ProductID, 
      prd.Name, 
      sal.TotalSales
   FROM 
      ProductSales sal
      JOIN Production.Product prd ON sal.ProductID = prd.ProductID
   WHERE 
      TotalSales > 1000000
      and OrderYear = 2004

If this example doesn't help to highlight the simplifications that you can achieve with CTEs, read on—the PIVOT functionality should serve to help make this clear.

Previous Page

Next Page

---

• Download The Code for this Article

---