Browse DevX
Sign up for e-mail newsletters from DevX


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

Sometimes it's the little things that can simplify your life the most. Learn to combine SQL Server's PIVOT and UNPIVOT commands with Common Table Expressions to make your T-SQL code easier to read and produce more useful crosstabbed results.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Thanks for your registration, follow us on our social networks to keep up-to-date