UNPIVOT Statement

Introduction

The UNPIVOT statement is used for changing columns into rows in a SQL Query (Normalization). The UNPIVOT Statement is generally written in this form:

• SELECT
    [DataType]
  ,   [DataValue]
  ,   [UnpivotedCol1]
  ,   [UnpivotedCol2]
  ,   [UnpivotedCol3]
  
• FROM table
• UNPIVOT
  (
    [DataValue] FOR [DataType]
    IN ([UnpivotedCol1],[UnpivotedCol1],[UnpivotedCol3],etc..)
  )
  AS Alias

Beginning Example - Example #1

Download This Script
This example will use the following table: SELECT Country, Sales, Expenses, Taxes, Profit
FROM #table1234 Here is a resultset from the above SELECT query:

Now we can use the UNPIVOT Keyword to normalize the result. This will turn the columns of Sales, Expenses, Taxes and Profit into Rows. Each column will become a row from the existing row. A Single row be created for each column, for each country.

SELECT Country, Amount.Amount, Amount.AmountType
FROM #table1234
  UNPIVOT (Amount for AmountType in (Sales, Expenses,Taxes,Profit)) as Amount
Here is a resultset from the above UNPIVOT query:

If for the above example, we just want the Sales and Expenses rows for each country, then we can use this query:

SELECT Country, Amount.Amount, Amount.AmountType
FROM #table1234
  UNPIVOT (Amount for AmountType in (Sales, Expenses)) as Amount
Here is a resultset from the above UNPIVOT query: