SQL Server UNPIVOT Explained

Sometimes you need to do the reverse of pivoting – take data that’s spread across multiple columns and convert it back into rows. You might receive data in a wide format from Excel, need to normalize denormalized data for storage, or simply need to reshape data for a different type of analysis. Fortunately, SQL Server has the UNPIVOT operator which is designed for this very scenario.

Whereas PIVOT transforms rows into columns, UNPIVOT transforms column headers back into row values. This creates a narrower, longer dataset from a wide one.

Understanding What `UNPIVOT` Does

When you unpivot data, you’re taking multiple columns that contain similar types of information and collapsing them into two columns: one that holds the original column names as values, and another that holds the actual data from those columns.

Imagine you have a spreadsheet showing quarterly sales with separate columns for Q1, Q2, Q3, and Q4. After unpivoting, you’d have one row per quarter instead, with a column indicating which quarter it is and another column showing the sales figure.

Simple Example

Here’s an example that demonstrates how UNPIVOT works.

We’ll start with data that’s already in a pivoted format. This will be a deployment dashboard showing application status across different environments in separate columns.

Sample Data

Here’s the data we’ll use for the example:

CREATE TABLE DeploymentDashboard (
    ApplicationName VARCHAR(50),
    Development VARCHAR(20),
    Testing VARCHAR(20),
    Staging VARCHAR(20),
    Production VARCHAR(20)
);

INSERT INTO DeploymentDashboard (ApplicationName, Development, Testing, Staging, Production)
VALUES 
    ('OrderAPI', 'Deployed', 'Deployed', 'Deployed', 'Pending'),
    ('CustomerPortal', 'Deployed', 'Deployed', 'Failed', 'NotStarted'),
    ('InventoryService', 'Deployed', 'Deployed', 'Deployed', 'Deployed'),
    ('PaymentGateway', 'Deployed', 'Pending', 'Pending', 'NotStarted');

SELECT * FROM DeploymentDashboard;

Output:

ApplicationName   Development  Testing   Staging   Production
----------------  -----------  --------  --------  ----------
OrderAPI          Deployed     Deployed  Deployed  Pending   
CustomerPortal    Deployed     Deployed  Failed    NotStarted
InventoryService  Deployed     Deployed  Deployed  Deployed  
PaymentGateway    Deployed     Pending   Pending   NotStarted

This data has one row per application with the deployment status for each environment in separate columns. We want to transform it into a normalized format with one row per application-environment combination.

Basic `UNPIVOT` Query

Here’s the UNPIVOT query that transforms our dashboard data:

SELECT ApplicationName, Environment, StatusCode
FROM DeploymentDashboard
UNPIVOT (
    StatusCode FOR Environment IN ([Development], [Testing], [Staging], [Production])
) AS UnpivotTable
ORDER BY ApplicationName, Environment;

Result:

ApplicationName   Environment  StatusCode
----------------  -----------  ----------
CustomerPortal    Development  Deployed  
CustomerPortal    Production   NotStarted
CustomerPortal    Staging      Failed    
CustomerPortal    Testing      Deployed  
InventoryService  Development  Deployed  
InventoryService  Production   Deployed  
InventoryService  Staging      Deployed  
InventoryService  Testing      Deployed  
OrderAPI          Development  Deployed  
OrderAPI          Production   Pending   
OrderAPI          Staging      Deployed  
OrderAPI          Testing      Deployed  
PaymentGateway    Development  Deployed  
PaymentGateway    Production   NotStarted
PaymentGateway    Staging      Pending   
PaymentGateway    Testing      Pending

It’s almost as if we’ve expanded a compressed file. It suddenly looks like a lot more data than we had before, but it’s actually the same data. It’s just presented in a different way.

Let’s break this down. Unlike PIVOT, you don’t need a subquery. You can unpivot directly from your source table. The UNPIVOT clause has two main components:

First is the value column name (StatusCode). This is what you want to call the column that will hold the actual data from your unpivoted columns.
Second is the FOR clause with the IN list. Here you specify the new column name for the identifiers (Environment) and list which columns to unpivot (Development, Testing, Staging, Production).

The result is a narrow table where each application-environment pair gets its own row, with the environment name and status clearly separated into distinct columns.

How `UNPIVOT` Handles NULL Values

One thing about UNPIVOT is that it automatically excludes rows where the value is NULL. This can be helpful or problematic depending on your needs.

Let’s see this in action:

-- Add a row with some NULL values
INSERT INTO DeploymentDashboard (ApplicationName, Development, Testing, Staging, Production)
VALUES ('ReportingService', 'Deployed', NULL, NULL, 'NotStarted');

-- UNPIVOT will skip the NULL columns
SELECT ApplicationName, Environment, StatusCode
FROM DeploymentDashboard
UNPIVOT (
    StatusCode FOR Environment IN ([Development], [Testing], [Staging], [Production])
) AS UnpivotTable
WHERE ApplicationName = 'ReportingService'
ORDER BY Environment;

Result:

ApplicationName   Environment  StatusCode
----------------  -----------  ----------
ReportingService  Development  Deployed  
ReportingService  Production   NotStarted

This query returns only two rows for ReportingService – Development and Production. The Testing and Staging columns get skipped entirely because they contain NULL values. If you need to preserve those NULL values as actual rows, you’ll need to use a different approach like CROSS APPLY with a VALUES clause, which we’ll cover shortly.

Unpivoting Multiple Column Sets

Sometimes you might have multiple sets of columns that you want to unpivot simultaneously. For example, you might have both a status and a deployment date for each environment:

CREATE TABLE DeploymentTracking (
    ApplicationName VARCHAR(50),
    Dev_Status VARCHAR(20),
    Dev_Date DATE,
    Prod_Status VARCHAR(20),
    Prod_Date DATE
);

INSERT INTO DeploymentTracking (ApplicationName, Dev_Status, Dev_Date, Prod_Status, Prod_Date)
VALUES 
    ('OrderAPI', 'Deployed', '2024-01-15', 'Pending', NULL),
    ('CustomerPortal', 'Deployed', '2024-01-20', 'Deployed', '2024-02-10'),
    ('InventoryService', 'Deployed', '2024-01-10', 'Deployed', '2024-01-25');

SELECT * FROM DeploymentTracking;

Output:

ApplicationName   Dev_Status  Dev_Date                  Prod_Status  Prod_Date               
----------------  ----------  ------------------------  -----------  ------------------------
OrderAPI          Deployed    2024-01-15T00:00:00.000Z  Pending      null                    
CustomerPortal    Deployed    2024-01-20T00:00:00.000Z  Deployed     2024-02-10T00:00:00.000Z
InventoryService  Deployed    2024-01-10T00:00:00.000Z  Deployed     2024-01-25T00:00:00.000Z

Here’s how you could unpivot that:

SELECT ApplicationName, Environment, Status, DeploymentDate
FROM (
    SELECT ApplicationName, Dev_Status, Prod_Status, Dev_Date, Prod_Date
    FROM DeploymentTracking
) AS SourceData
UNPIVOT (
    Status FOR Environment IN ([Dev_Status], [Prod_Status])
) AS UnpivotStatus
UNPIVOT (
    DeploymentDate FOR DateColumn IN ([Dev_Date], [Prod_Date])
) AS UnpivotDate
WHERE (Environment = 'Dev_Status' AND DateColumn = 'Dev_Date')
   OR (Environment = 'Prod_Status' AND DateColumn = 'Prod_Date');

Result:

ApplicationName   Environment  Status    DeploymentDate          
----------------  -----------  --------  ------------------------
OrderAPI          Dev_Status   Deployed  2024-01-15T00:00:00.000Z
CustomerPortal    Dev_Status   Deployed  2024-01-20T00:00:00.000Z
CustomerPortal    Prod_Status  Deployed  2024-02-10T00:00:00.000Z
InventoryService  Dev_Status   Deployed  2024-01-10T00:00:00.000Z
InventoryService  Prod_Status  Deployed  2024-01-25T00:00:00.000Z

This gets complicated quickly. You’re unpivoting twice and then filtering to match up the right pairs. We’ve also got the issue with the NULL value preventing the OrderAPI’s production status from being returned. Honestly, for scenarios like this, using CROSS APPLY tends to be cleaner and more maintainable.

Alternative Approach with `CROSS APPLY`

Sometimes you might find that CROSS APPLY offers more flexibility and is a lot cleaner:

SELECT ApplicationName, Environment, Status, DeploymentDate
FROM DeploymentTracking
CROSS APPLY (
    VALUES 
        ('Development', Dev_Status, Dev_Date),
        ('Production', Prod_Status, Prod_Date)
) AS UnpivotTable(Environment, Status, DeploymentDate)
ORDER BY ApplicationName, Environment;

Result:

ApplicationName   Environment  Status    DeploymentDate          
----------------  -----------  --------  ------------------------
CustomerPortal    Development  Deployed  2024-01-20T00:00:00.000Z
CustomerPortal    Production   Deployed  2024-02-10T00:00:00.000Z
InventoryService  Development  Deployed  2024-01-10T00:00:00.000Z
InventoryService  Production   Deployed  2024-01-25T00:00:00.000Z
OrderAPI          Development  Deployed  2024-01-15T00:00:00.000Z
OrderAPI          Production   Pending   null

This is basically the same result as the previous example, except that this time we’ve got the Production environment for OrderAPI (along with its NULL value). You’ll recall that the UNPIVOT example hid this row (due to the NULL value).

While we’re at it, here’s CROSS APPLY against the DeploymentDashboard table (the earlier table):

SELECT ApplicationName, Environment, StatusCode
FROM DeploymentDashboard
CROSS APPLY (
    VALUES 
        ('Development', Development),
        ('Testing', Testing),
        ('Staging', Staging),
        ('Production', Production)
) AS UnpivotTable(Environment, StatusCode)
ORDER BY ApplicationName, Environment;

Result:

ApplicationName   Environment  StatusCode
----------------  -----------  ----------
CustomerPortal    Development  Deployed  
CustomerPortal    Production   NotStarted
CustomerPortal    Staging      Failed    
CustomerPortal    Testing      Deployed  
InventoryService  Development  Deployed  
InventoryService  Production   Deployed  
InventoryService  Staging      Deployed  
InventoryService  Testing      Deployed  
OrderAPI          Development  Deployed  
OrderAPI          Production   Pending   
OrderAPI          Staging      Deployed  
OrderAPI          Testing      Deployed  
PaymentGateway    Development  Deployed  
PaymentGateway    Production   NotStarted
PaymentGateway    Staging      Pending   
PaymentGateway    Testing      Pending   
ReportingService  Development  Deployed  
ReportingService  Production   NotStarted
ReportingService  Staging      null      
ReportingService  Testing      null

This approach explicitly creates rows for each column you want to unpivot. The VALUES clause generates a row for each environment-status pair, and CROSS APPLY applies this to each row in your source table. Again, this preserves NULL values (unlike UNPIVOT).

The CROSS APPLY method is often more readable, especially when you’re working with columns that have different names or when you need to apply transformations during the unpivot operation.

Unpivoting Numeric Data

Unpivoting works the same way with numeric data. Here’s an example with monthly sales figures:

CREATE TABLE QuarterlySales (
    ProductName VARCHAR(50),
    Q1_Revenue DECIMAL(10,2),
    Q2_Revenue DECIMAL(10,2),
    Q3_Revenue DECIMAL(10,2),
    Q4_Revenue DECIMAL(10,2)
);

INSERT INTO QuarterlySales (ProductName, Q1_Revenue, Q2_Revenue, Q3_Revenue, Q4_Revenue)
VALUES 
    ('Widget Pro', 15000.00, 18000.00, 22000.00, 19000.00),
    ('Gadget Plus', 12000.00, 13500.00, 14200.00, 16000.00),
    ('Thingamajig', 8000.00, 9500.00, 11000.00, 10500.00);

SELECT * FROM QuarterlySales;

Output:

ProductName  Q1_Revenue  Q2_Revenue  Q3_Revenue  Q4_Revenue
-----------  ----------  ----------  ----------  ----------
Widget Pro   15000       18000       22000       19000     
Gadget Plus  12000       13500       14200       16000     
Thingamajig  8000        9500        11000       10500

Now let’s unpivot:

SELECT ProductName, Quarter, Revenue
FROM QuarterlySales
UNPIVOT (
    Revenue FOR Quarter IN ([Q1_Revenue], [Q2_Revenue], [Q3_Revenue], [Q4_Revenue])
) AS UnpivotTable
ORDER BY ProductName, Quarter;

Result:

ProductName  Quarter     Revenue
-----------  ----------  -------
Gadget Plus  Q1_Revenue  12000  
Gadget Plus  Q2_Revenue  13500  
Gadget Plus  Q3_Revenue  14200  
Gadget Plus  Q4_Revenue  16000  
Thingamajig  Q1_Revenue  8000   
Thingamajig  Q2_Revenue  9500   
Thingamajig  Q3_Revenue  11000  
Thingamajig  Q4_Revenue  10500  
Widget Pro   Q1_Revenue  15000  
Widget Pro   Q2_Revenue  18000  
Widget Pro   Q3_Revenue  22000  
Widget Pro   Q4_Revenue  19000

The Quarter column contains the literal column names as they appear in the IN clause (Q1_Revenue, Q2_Revenue, and so on). If you want cleaner values like Q1 or Quarter 1, you’ll need to transform them after unpivoting using REPLACE() or a CASE statement.

Cleaning Up Column Names After `UNPIVOT`

When your source columns have prefixes or suffixes that you don’t want in the unpivoted output, you can clean them up with string functions:

SELECT 
    ProductName, 
    REPLACE(Quarter, '_Revenue', '') AS Quarter,
    Revenue
FROM QuarterlySales
UNPIVOT (
    Revenue FOR Quarter IN ([Q1_Revenue], [Q2_Revenue], [Q3_Revenue], [Q4_Revenue])
) AS UnpivotTable
ORDER BY ProductName, Quarter;

Result:

ProductName  Quarter  Revenue
-----------  -------  -------
Gadget Plus  Q1       12000  
Gadget Plus  Q2       13500  
Gadget Plus  Q3       14200  
Gadget Plus  Q4       16000  
Thingamajig  Q1       8000   
Thingamajig  Q2       9500   
Thingamajig  Q3       11000  
Thingamajig  Q4       10500  
Widget Pro   Q1       15000  
Widget Pro   Q2       18000  
Widget Pro   Q3       22000  
Widget Pro   Q4       19000

Now the Quarter column shows Q1, Q2, Q3, and Q4 instead of the full column names. You can use any string manipulation function here. For example, you could use SUBSTRING(), LEFT(), RIGHT(), REPLACE(), or even a CASE statement for more complex transformations.

When to Use `UNPIVOT`

UNPIVOT is ideal when you’re receiving data in a denormalized format and need to normalize it for storage or analysis. It’s particularly useful when importing data from spreadsheets where people have organized information across columns rather than down rows, when restructuring legacy data that was designed for reporting rather than storage, or when you need to transform data from one system’s format to match another system’s expectations.

However, UNPIVOT isn’t always the best choice. If you need to preserve NULL values as actual rows, CROSS APPLY is usually better. If your unpivot logic is complex with lots of conditional transformations, writing it out explicitly with UNION ALL might be clearer. And if you’re unpivoting many columns, the IN clause can get unwieldy. In those cases, dynamic SQL might be worth considering.

Quick Summary

The UNPIVOT operator gives you a clean, readable way to transform columnar data back into rows. It’s straightforward for simple cases and handles the common scenario of collapsing similar columns into a normalized structure. Understanding both UNPIVOT and its alternative approaches like CROSS APPLY ensures you can pick the right tool for whatever data transformation challenge you’re facing.

Understanding What UNPIVOT Does

Simple Example

Sample Data

Basic UNPIVOT Query

How UNPIVOT Handles NULL Values

Unpivoting Multiple Column Sets

Alternative Approach with CROSS APPLY

Unpivoting Numeric Data

Cleaning Up Column Names After UNPIVOT

When to Use UNPIVOT

Quick Summary

Understanding What `UNPIVOT` Does

Basic `UNPIVOT` Query

How `UNPIVOT` Handles NULL Values

Alternative Approach with `CROSS APPLY`

Cleaning Up Column Names After `UNPIVOT`

When to Use `UNPIVOT`