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, Val
from PivotExample2 a
unpivot
(
   Val for Val1 in (AA, BB, CC, DD)
) as up

The above sql results in the following output:

GrpA	Val
XXX	9
XXX	11
XXX	23
XXX	16
YYY	30
YYY	18
YYY	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.