UnPivot Example

The following is an example of how to use the SQL UNPIVOT function. This function is almost the opposite of the PIVOT function.

The UNPIVOT function takes specified columns and output them as rows.

Start with a table, [PivotExample2], with the following data:

RowID	GrpA	AA	BB	CC	DD
1	XXX	9	11	23	16
2	YYY	30	18	8	NULL

To takes the values in columns [AA], [BB], [CC], & [DD], use the following query:

select up.GrpA       -- selects the grouping value
   , up.valHeader       -- outputs the column header
   , up.valColumn       -- outputs the column value
from PivotExample2 a
unpivot
(
   valColumn for valHeader in (AA, BB, CC, DD) -- Do NOT put these values in quotes.
) as up

The above sql results in the following output:

GrpA	valHeader	valColumn
XXX	AA	9
XXX	BB	11
XXX	CC	23
XXX	DD	16
YYY	AA	30
YYY	BB	18
YYY	CC	8

This source data is the same as the output from the PIVOT example. The CC column value in the GrpA row is the sum of 9, 10, & 4. However, when the data is un-pivoted, it is output as a single row with a value of 23.

There is no way for SQL to know which values were aggregated to make up the value in the column. In this respect, the UnPivot function is not exactly the opposite of the Pivot function.

Click here for a PIVOT example.