T-SQL Tutorials - TSQLTutorials.com

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
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 25000001250000 500000 750000
North America 2000000250000 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



Microsoft SQL Server 2014 Standard Edition Server w/20 CAL’s

Price: $641.99
Buy It Now
Microsoft SQL Server 2008 R2 Standard Server License key +5CAL FactorySealedPack

Price: $495
Buy It Now
Microsoft SQL Server 2012 Standard 10CAL +Server License Key Factory Sealed Pack

Price: $899
Buy It Now
Microsoft SQL Server 2012 Standard with SP3 - 25 CAL License - SAME DAY DOWNLOAD

Price: $700
Buy It Now
Lenovo Microsoft SQL Server 2014 - License - 1 User CAL - OEM

Price: $169.99
Buy It Now
Microsoft SQL Server Standard 2014 + 10 Cal

Current Price: $122.5
Current Bids: 8







Copyright 2017 - TSQLTutorials.com