Programming Microsoft® SQL Server™ 2008

The PIVOT and UNPIVOT Operators

Let’s face it—users usually want to see data in tabular format, which is a bit of a challenge given that data in SQL Server is most often stored in a highly relational form. PIVOT is a T-SQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional crosstab query.

Using PIVOT is easy. In your SELECT statement, you specify the values you want to pivot on. The following example in the AdventureWorks2008 database uses the order years (calculated using the DatePart function) as the columns. The FROM clause looks normal except for the PIVOT statement. This statement creates the value you want to show in the rows of the newly created columns. This example uses the aggregate ...