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
Note: You must use brackets around each of the Pivot Column Values
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:
| Country | Sales | Expenses | Taxes | Profit |
|---|
| Asia | 800000 | 350000 | 100000 | NULL |
| Eupore | 2500000 | 1250000 | 500000 | 750000 |
| North America | 2000000 | 250000 | 250000 | 500000 |
| South America | 500000 | 250000 | NULL | NULL |
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:
| Country | Amount | AmountType |
|---|
| Asia | 800000 | Sales |
| Asia | 350000 | Expenses |
| Asia | 100000 | Taxes |
| Europe | 2500000 | Sales |
| Europe | 1250000 | Expenses |
| Europe | 500000 | Taxes |
| Europe | 750000 | Profit |
| North America | 2000000 | Sales |
| North America | 250000 | Expenses |
| North America | 250000 | Taxes |
| North America | 500000 | Profit |
| South America | 500000 | Sales |
| South America | 250000 | Expenses |
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:
| Country | Amount | AmountType |
|---|
| Asia | 800000 | Sales |
| Asia | 350000 | Expenses |
| Europe | 2500000 | Sales |
| Europe | 1250000 | Expenses |
| North America | 2000000 | Sales |
| North America | 250000 | Expenses |
| South America | 500000 | Sales |
| South America | 250000 | Expenses |
|
