UNPIVOT
The UNPIVOT operator takes a table expression (table, procedure, or JOIN) and rotates columns into rows.
Syntax
unpivot_clause : table_reference UNPIVOT ( ( INCLUDE | EXCLUDE ) NULLS )?
LEFT_PAREN ( identifier | LEFT_PAREN identifier ( COMMA identifier )+ RIGHT_PAREN )
unpivot_for_clause
unpivot_in_clause
RIGHT_PAREN (AS)? identifier
unpivot_for_clause : FOR identifier
unpivot_in_clause : IN LEFT_PAREN ( column ( AS string_constant )? ( COMMA column ( AS string_constant )? )*
| unpivot_multiple_columns ( COMMA unpivot_multiple_columns )*
)
RIGHT_PAREN
unpivot_multiple_columns : LEFT_PAREN column ( COMMA column )* RIGHT_PAREN
( AS string_constant )?
Remarks
• The table expression can be a table, procedure, or JOIN.
• The result of the table expression will be fed into the UNPIVOT operator
Example for Projections
The UNPIVOT operator introduces new projections specified by the identifiers immediately following the UNPIVOT and FOR keyword
UNPIVOT (LabelOldColumnValues .... FOR LabeOldColumnNames
LabelOldColumnValues and LabeOldColumnNames will become the two new columns. LabeOldColumnNames will contain the names of the unpivoted columns. LabelOldColumnValues will contain the unpivoted column's values.
UNPIVOT (LabelOldColumnValues FOR LabeOldColumnNames IN (columnA, columnB)
Example for Renaming Columns
Old column names can be renamed by specifing the new name as a string constant in the IN clause.
In the example below, instead of the strings 'columnA' and 'columnB', we will see the strings 'rename1' and 'rename2'
UNPIVOT ... FOR LabeOldColumnNames IN (columnA as 'rename1', columnB as 'rename2')
O LabeOldColumnNames LabelOldColumnValues
1 rename1 a1
1 rename2 a2
2 rename1 b1
2 rename2b2
3 rename1 c1
3 rename2 c2
Example for Multiple Column Sets
UNPIVOT ( (LabelOldColumnValues1, LabelOldColumnValues2, LabelOldColumnValues3) FOR
LabeOldColumnNames IN ( (columnA, columnB, columnC), (columnD, columnE, columnF) )
O columnA columnB columnC columnD columnE columnF
1 a1 b1 c1 d1 e1 f1
2 a2 b2 c2 d2 e2 f2
3 b3 c3 d3 e3 f3
will be rotated to
O LabeOldColumnNames LabelOldColumnValues1 LabelOldColumnValues2 LabelOldColumnValues3
- ------------ ------ ------
1 columnA_columnB_columnC a1 b1 c1
1 columnD_columnE_columnF d1 e1 f1
2 columnA_columnB_columnC a2 b2 c2
2 columnD_columnE_columnF d2 e2 f2
3 columnA_columnB_columnC a3 b3 c3
3 columnD_columnE_columnF d3 e3 f3
Example for Renaming Multiple Column Sets
UNPIVOT ( (LabelOldColumnValues1, LabelOldColumnValues2, LabelOldColumnValues3) FOR
LabeOldColumnNames IN ( (columnA, columnB, columnC) as 'gold', (columnD, columnE, columnF) as 'silver')
O LabeOldColumnNames LabelOldColumnValues1 LabelOldColumnValues2 LabelOldColumnValues3
1 gold a1 b1 c1
1 silver d1 e1 f1
2 gold a2 b2 c2
2 silver d2 e2 f2
3 gold a3 b3 c3
3 silver d3 e3 f3