Blog : Using PIVOT and UNPIVOT

Using PIVOT and UNPIVOT

SQL Server 2008 R2

Other Versions

  • SQL Server 2008
  • SQL Server 2005

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Note

When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher. For information about how to set the database compatibility level, see sp_dbcmptlevel (Transact-SQL).

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

The following is annotated syntax for PIVOT.

SELECT ,

 [first pivoted column] AS ,

 [second pivoted column] AS ,

 ...

 [last pivoted column] AS

FROM

 (