When you need to rotate data from rows into columns, you can use the new T-SQL PIVOT operator that SQL Server 2005 provides. In my July and August 2004 columns, "Pivot (or Unpivot) Your Data" (InstantDoc ID 42901) and "Dynamic Pivoting" (InstantDoc ID 43140), I describe how to use the PIVOT operator in different scenarios. But, you might also face situations in which you need to rotate data from columns into rows-or unpivot data. The most common scenario that requires unpivoting is normalizing data that doesn't meet the first normal form (1NF), which means the table has repeating groups. For example, a table that stores a group of elements in each row instead of storing each element in a separate row doesn't meet the first normal form. A table that has repeating groups is usually a result of poor database design--or it might be an intermediate staging table you created in SQL Server to import data from a nonrelational environment (e.g., text files, spreadsheets). Once you import the data, you unpivot it and insert the result into a normalized table. This month, I'll create a table that doesn't meet the first normal form, then show you how to query it and generate a normalized output in both SQL Server 2005 and SQL Server 2000.
Run Listing 1 to create and populate the MonthlySales table, which I use in my examples. The MonthlySales table contains monthly sales quantities. In separate columns, each row contains an order year and 12 monthly quantities. Figure 1 shows the MonthlySales table. Now, I'll show you how to query the MonthlySales table to generate a separate row for each month that contains the sales year, month, and quantity.
Unpivoting in SQL Server 2000
SQL Server 2000 doesn't have a built-in UNPIVOT operator. To unpivot the data in the MonthlySales table, you must customize a solution. To simplify your solution, you can break the problem into three steps: first, duplicate each row from MonthlySales 12 times (once for each sales month); second, extract the corresponding quantity for each month; and third, eliminate rows that have NULL quantities. Let's start with the first step-generating duplicates. To duplicate the rows in the MonthlySales table 12 times, write a query that performs a cross join between the MonthlySales table and an auxiliary table that contains all 12 sales months. You can either use a real auxiliary table or a virtual one. Listing 2 creates a virtual table.
Now that you've generated a duplicate row for each sales month, you can code the second step. Here you need to extract the quantity that corresponds to the sales month by using a CASE expression in the SELECT list. The CASE expression checks the sales month-if it's 1, the CASE expression returns column \[1\], which holds January's quantity. If it's 2, the CASE expression returns column \[2\], which holds February's quantity, and so on. Listing 3 contains these first two steps, and Figure 2 shows Listing 3's results.
Notice that Figure 2 shows a NULL quantity for each sales month that has no activity. Listing 4 shows how to resolve the third step-eliminating rows that have NULLs in the qty column. Use Listing 4's code to create a derived table D and specify WHERE qty IS NOT NULL in the outer query's filter, thus eliminating rows that have NULLs in the qty column. Figure 3 shows the result of running Listing 4.
SQL Server 2005 UNPIVOT
SQL Server 2005 provides a built-in UNPIVOT operator. The UNPIVOT operator looks similar to the PIVOT operator. For the first argument, pass to UNPIVOT a name for the result column that will hold the values you rotate-in this case, qty. For the second argument, pass to UNPIVOT a name for the result column that will hold the names of the columns you rotate-in this case, salesmonth. The list of the source column names follows the second argument:
(\[1\],\[2\],\[3\],\[4\],\[5\],\[6\],\[7\],\[8\],\[9\],\[10\],\[11\],\[12\]). Note that you'll use the source column names as values in the target salesmonth column, and their data types will be character-based. Because month numbers are usually represented as integer values, you might want to explicitly convert the values to integers. Listing 5 returns the desired result.
There isn't a great performance difference between the SQL Server 2000 unpivoting technique and the SQL Server 2005 UNPIVOT technique. However, the SQL Server 2005 technique is simpler and shorter than the SQL Server 2000 technique because you have the built-in UNPIVOT operator-one step instead of three. With SQL Server 2005, there's no need to cross the base table with an auxiliary table to generate duplicates; no need to use a CASE expression to extract the sales quantity; no need to eliminate NULLs.
PIVOT & UNPIVOT
SQL Server 2005 brings you shorter, simpler ways to express what you can achieve with SQL Server 2000 queries. Pivoting in SQL Server 2005 gives you neither performance advantages nor dynamic capabilities. But don't underestimate the ability to write simpler, shorter code. You can maintain the SQL Server 2005 code more easily and it's less prone to errors.