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