Figure 10-9. The partial results of comparing UNION to GROUPING SETS in Font

Generate PDF 417 in Font Figure 10-9. The partial results of comparing UNION to GROUPING SETS

Normally, a query displays the data in a way that is similar to how it looks in a table, often with the column headers being the actual names of the columns within the table.

A pivoted query displays the values of one column as column headers instead. For example, you could display the sum of the sales by month so that the month names are column headers. Each row would then contain the data by year with the sum for each month displayed from left to right. This section shows how to write pivoted queries with two techniques: CASE and PIVOT.

Before SQL Server 2005, many developers used the CASE function to create pivoted results. (See The Case Function section in 3 to learn more about CASE.) In fact, many still use this technique. Essentially, you use several CASE expressions in the query, one for each pivoted column header. For example, the query will have a CASE expression checking to see whether the month of the order date is January. If the order does occur in January, supply the total sales value. If not, supply a zero. For each row, the data ends up in the correct column where it can be aggregated. Here is the syntax for using CASE to pivot data: CASE <col1>,SUM(CASE <col3> WHEN <value1> THEN <col2> ELSE 0 END) AS <alias1>, SUM(CASE <col3> WHEN <value2> THEN <col2> ELSE 0 END) AS <alias2>, SUM(CASE <col3> WHEN <value3> THEN <col2> ELSE 0 END) AS <alias3> FROM <table1> GROUP BY <col1> Type in and execute Listing 10-10 to learn how to pivot data using CASE. Listing 10-10. Using CASE to Pivot Data USE AdventureWorks2008; GO SELECT YEAR(OrderDate) AS OrderYear, ROUND(SUM(CASE MONTH(OrderDate) WHEN AS Jan, ROUND(SUM(CASE MONTH(OrderDate) WHEN AS Feb, ROUND(SUM(CASE MONTH(OrderDate) WHEN AS Mar, ROUND(SUM(CASE MONTH(OrderDate) WHEN AS Apr, ROUND(SUM(CASE MONTH(OrderDate) WHEN AS May, ROUND(SUM(CASE MONTH(OrderDate) WHEN AS Jun FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY OrderYear;

1 THEN TotalDue ELSE 0 END),0) 2 THEN TotalDue ELSE 0 END),0) 3 THEN TotalDue ELSE 0 END),0) 4 THEN TotalDue ELSE 0 END),0) 5 THEN TotalDue ELSE 0 END),0) 6 THEN TotalDue ELSE 0 END),0)

Figure 10-10 shows the results. To save space in the results, the statement calculates the totals only for the months January through June and uses the ROUND function. The GROUP BY clause contains just the YEAR(OrderDate) expression. You might think that you need to group by month as well, but this query doesn t group by month. It just includes each TotalDue value in a different column depending on the month.

Microsoft introduced the PIVOT function with SQL Server 2005. In my opinion, the PIVOT function is more difficult to understand than using CASE to produce the same results. Just like CASE, you have to hard-code the column names. This works fine when the pivoted column names will never change, such as the months of the year. When the query bases the pivoted column on data that changes over time, such as employee or department names, the query must be modified each time that data changes. Here is the syntax for PIVOT: SELECT <groupingCol>, <pivotedValue1> [AS <alias1>], <pivotedValue2> [AS <alias2>] FROM (SELECT <groupingCol>, <value column>, <pivoted column>) AS <queryAlias> PIVOT ( <aggregate function>(<value column>) FOR <pivoted column> IN (<pivotedValue1>,<pivotedValue2>) ) AS <pivotAlias> [ORDER BY <groupingCol>] The SELECT part of the query lists any nonpivoted columns along with the values from the pivoted column. These values from the pivoted column will become the column names in your query. You can use aliases if you want to use a different column name than the actual value. For example, if the column names will be the month numbers, you can alias with the month names. This syntax uses a derived table, listed after the word FROM, as the basis of the query. See the Derived Tables section in 4 to review derived tables. Make sure that you only list columns that you want as grouping levels, the pivoted column, and the column that will be summarized in this derived table. Adding other columns to this query will cause extra grouping levels and unexpected results. The derived table must be aliased, so don t forget that small detail.

Tip It is possible to use a CTE to write this query instead of a derived table. See the article Create Pivoted