UNPIVOT in SQL

The UNPIVOT operator in SQL performs the opposite of the PIVOT operator: it rotates columns into rows. It transforms columns that hold similar data (like monthly sales figures) into two new columns: one column containing the original column names (the pivot column) and another column containing the values (the data column)

Goal: Columns to Rows (Lengthen the table)

Input Data: Denormalized/Wide format (e.g., summary report)

Main Use: Normalization and Data Analysis (e.g., filtering)

Output Shape: Longer (more rows) but Narrower (fewer columns)

Key Requirement for UNPIVOT

• It does not require an aggregation function.
• NULL values in the source columns are typically eliminated in the unpivoted output.

Scenario: Restructuring Monthly Financial Data 📈

Imagine you have a table named QuarterlySales that resulted from a previous PIVOT operation or was imported this way. It shows sales figures for three months (Jan, Feb, Mar) as separate columns.

EmployeeName Jan Feb Mar

Alice 1000 1500 1300

Bob 1200 1100 1400

The UNPIVOT Goal

Your goal is to transform this wide table back into a long, normalized format, where:

1. The sales figures (1000, 1500, etc.) are all in a single column named TotalSales.
2. The month names (Jan, Feb, Mar) are all in a single column named SalesMonth.

EmployeeName SalesMonth TotalSales

Bob Jan 1200

Bob Feb 1100

Bob Mar 1400

SQL UNPIVOT Implementation

The SQL query to achieve this using the UNPIVOT operator would look like this (using SQL Server syntax):

SELECT EmployeeName, SalesMonth, TotalSales
FROM QuarterlySales
UNPIVOT (
    TotalSales     -- 1. The name of the new 'data' column (the values)
    FOR SalesMonth -- 2. The name of the new 'pivot' column (the column headers)
    IN ([Jan], [Feb], [Mar]) -- 3. The list of existing columns to rotate into rows
) AS UnpivotTable;

Explanation of the UNPIVOT Clauses:

• **TotalSales: This is the Data Column**. It specifies the name of the new column that will hold all the cell values from the original columns (Jan, Feb, Mar).
• **FOR SalesMonth: This is the Pivot Column**. It specifies the name of the new column that will hold the names of the original columns being rotated (i.e., 'Jan', 'Feb', 'Mar').
• **IN ([Jan], [Feb], [Mar]): This lists the specific columns** in the source table (QuarterlySales) that you want to unpivot (turn into rows).

The result of this query is the desired long, normalized table, which is often easier to query, filter, and aggregate further using standard GROUP BY clauses.

When to use UNPIVOT

UNPIVOT is primarily used for normalization and analysis:

• Data Normalization: Converting non-standard, “wide” tables (often from reports or external systems) into a standard normalized format that adheres to relational database principles.
• Simplified Queries: It allows you to write queries like “Find all sales greater than 1200, regardless of the month,” which would be difficult to write on the “wide” table without checking every month column individually.
• Data Warehousing: Preparing data for loading into data warehouses where a long, transactional format is often preferred.