Pivoting and Unpivoting Data in SQL

Picture this scenario: You're analyzing quarterly sales data, and your finance team hands you a spreadsheet where each salesperson has four separate columns — one for each quarter's sales figures. But your dashboard software needs the data in a different format: one row per salesperson per quarter, with separate columns for the salesperson, quarter, and sales amount. Welcome to the world of data pivoting and unpivoting — one of the most practical yet initially confusing aspects of working with SQL.

If you've ever found yourself staring at data that's organized "the wrong way" for your analysis, you're not alone. Data pivoting and unpivoting are fundamental operations that transform how your data is structured. Pivoting takes rows and turns them into columns, while unpivoting does the reverse — it takes columns and converts them into rows. These operations are essential for preparing data for analysis, reporting, and visualization tools that expect data in specific formats.

What you'll learn:

• What pivoting and unpivoting mean and when to use each operation
• How to write PIVOT queries to transform rows into columns
• How to write UNPIVOT queries to transform columns into rows
• How to handle dynamic pivoting scenarios with varying column names
• Common pitfalls and how to troubleshoot pivot operations
• Real-world applications for reshaping sales, survey, and time-series data

Prerequisites

Before diving into pivoting and unpivoting, you should be comfortable with:

• Basic SQL SELECT statements
• JOIN operations
• GROUP BY and aggregate functions (SUM, COUNT, AVG)
• Understanding of table structure and relational concepts

We'll be using SQL Server syntax primarily, but I'll point out differences for other database systems where relevant.

Understanding the Concept: Rows vs Columns

Before jumping into code, let's establish exactly what we mean by pivoting and unpivoting with a concrete example.

Imagine you work for a retail company, and you receive monthly sales data in this format:

-- Original "long" format data
CREATE TABLE monthly_sales (
    salesperson VARCHAR(50),
    month_name VARCHAR(20),
    sales_amount DECIMAL(10,2)
);

INSERT INTO monthly_sales VALUES
('Alice Johnson', 'January', 15000.00),
('Alice Johnson', 'February', 18000.00),
('Alice Johnson', 'March', 22000.00),
('Bob Smith', 'January', 12000.00),
('Bob Smith', 'February', 14000.00),
('Bob Smith', 'March', 16000.00),
('Carol Davis', 'January', 20000.00),
('Carol Davis', 'February', 19000.00),
('Carol Davis', 'March', 25000.00);

This is called "long" or "normalized" format — each measurement (monthly sales) occupies its own row. But what if your manager wants to see this data in a spreadsheet-style format where each salesperson appears once, with separate columns for each month? That's where pivoting comes in.

After pivoting, the data would look like this:

salesperson	January	February	March
Alice Johnson	15000	18000	22000
Bob Smith	12000	14000	16000
Carol Davis	20000	19000	25000

This is "wide" format — the month names have become column headers, and each salesperson's data fits on a single row.

Unpivoting works in reverse. If you started with the wide format and needed to convert it back to the long format (perhaps to load it into a database that expects normalized data), you'd use an UNPIVOT operation.

The PIVOT Operation: From Rows to Columns

Basic PIVOT Syntax

The PIVOT operation in SQL Server follows this general pattern:

SELECT [columns]
FROM (
    -- Source query that provides the data to pivot
    SELECT columns_to_pivot, pivot_column, value_column
    FROM source_table
) AS source_data
PIVOT (
    aggregate_function(value_column)
    FOR pivot_column IN ([value1], [value2], [value3], ...)
) AS pivot_table;

Let's break this down:

• Source query: Provides the raw data with exactly three types of columns you need
• pivot_column: The column whose distinct values will become new column headers
• value_column: The column containing the values that will populate the pivoted columns
• aggregate_function: How to combine values if there are multiple rows for the same combination

Your First PIVOT Query

Let's pivot our monthly sales data. We want salesperson names as rows, months as columns, and sales amounts as values:

SELECT salesperson, [January], [February], [March]
FROM (
    SELECT salesperson, month_name, sales_amount
    FROM monthly_sales
) AS source_data
PIVOT (
    SUM(sales_amount)
    FOR month_name IN ([January], [February], [March])
) AS pivot_table;

Here's what happens step by step:

1. Source query: We select the three essential columns — salesperson (our row identifier), month_name (what becomes columns), and sales_amount (the values)
2. PIVOT clause: We tell SQL to sum up sales_amount values, grouping by salesperson, and create separate columns for January, February, and March
3. Result: Each salesperson appears once, with their monthly sales spread across columns

Important: The column names in the IN clause ([January], [February], [March]) must exactly match the values in your pivot column. Case matters!

Handling Multiple Aggregations

What if you want to see both total sales and average sales for each month? You can't do multiple aggregations in a single PIVOT, but you can work around it:

-- First, create a helper query that calculates what we need
WITH sales_summary AS (
    SELECT 
        salesperson,
        'Total_' + month_name AS metric_month,
        sales_amount AS value
    FROM monthly_sales
    
    UNION ALL
    
    SELECT 
        salesperson,
        'Avg_' + month_name AS metric_month,
        sales_amount AS value
    FROM monthly_sales
)
SELECT salesperson, 
       [Total_January], [Total_February], [Total_March],
       [Avg_January], [Avg_February], [Avg_March]
FROM sales_summary
PIVOT (
    AVG(value)  -- Using AVG here since we're not actually averaging in this case
    FOR metric_month IN ([Total_January], [Total_February], [Total_March],
                        [Avg_January], [Avg_February], [Avg_March])
) AS pivot_table;

This approach creates separate columns for totals and averages by concatenating the metric type with the month name.

The UNPIVOT Operation: From Columns to Rows

Understanding UNPIVOT

UNPIVOT reverses the pivot operation. It's particularly useful when you receive data in wide format (like from Excel) and need to normalize it for database storage or analysis.

Let's say you received this quarterly sales data:

CREATE TABLE quarterly_sales_wide (
    salesperson VARCHAR(50),
    Q1_Sales DECIMAL(10,2),
    Q2_Sales DECIMAL(10,2),
    Q3_Sales DECIMAL(10,2),
    Q4_Sales DECIMAL(10,2)
);

INSERT INTO quarterly_sales_wide VALUES
('Alice Johnson', 45000.00, 52000.00, 48000.00, 55000.00),
('Bob Smith', 38000.00, 41000.00, 39000.00, 44000.00),
('Carol Davis', 51000.00, 49000.00, 53000.00, 58000.00);

To convert this to long format, you'd use UNPIVOT:

SELECT salesperson, quarter, sales_amount
FROM quarterly_sales_wide
UNPIVOT (
    sales_amount FOR quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
) AS unpivot_table;

This transforms the wide table into:

salesperson	quarter	sales_amount
Alice Johnson	Q1_Sales	45000.00
Alice Johnson	Q2_Sales	52000.00
Alice Johnson	Q3_Sales	48000.00
Alice Johnson	Q4_Sales	55000.00
Bob Smith	Q1_Sales	38000.00
...	...	...

Cleaning Up UNPIVOT Results

Notice that the quarter names include "_Sales" which might not be what you want. You can clean this up:

SELECT 
    salesperson, 
    REPLACE(quarter, '_Sales', '') AS quarter,
    sales_amount
FROM quarterly_sales_wide
UNPIVOT (
    sales_amount FOR quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
) AS unpivot_table;

Or even better, create more meaningful quarter names:

SELECT 
    salesperson,
    CASE quarter
        WHEN 'Q1_Sales' THEN 'Quarter 1'
        WHEN 'Q2_Sales' THEN 'Quarter 2'
        WHEN 'Q3_Sales' THEN 'Quarter 3'
        WHEN 'Q4_Sales' THEN 'Quarter 4'
    END AS quarter,
    sales_amount
FROM quarterly_sales_wide
UNPIVOT (
    sales_amount FOR quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
) AS unpivot_table;

Dynamic Pivoting: When You Don't Know the Columns

One limitation of the basic PIVOT syntax is that you must specify the column names in advance. But what if you're working with data where the pivot values change? For example, you might have sales data for different years, and you want to create a column for each year in your dataset.

Building Dynamic PIVOT with SQL

Here's a practical approach using dynamic SQL:

-- Sample data with varying years
CREATE TABLE yearly_sales (
    product VARCHAR(50),
    year_sold INT,
    total_sales DECIMAL(10,2)
);

INSERT INTO yearly_sales VALUES
('Laptop', 2021, 150000),
('Laptop', 2022, 180000),
('Laptop', 2023, 220000),
('Tablet', 2021, 90000),
('Tablet', 2022, 95000),
('Tablet', 2023, 105000),
('Phone', 2022, 300000),  -- Note: no 2021 data for phones
('Phone', 2023, 350000);

-- Dynamic pivot approach
DECLARE @columns NVARCHAR(MAX) = ''
DECLARE @sql NVARCHAR(MAX) = ''

-- Build the column list dynamically
SELECT @columns = STRING_AGG(QUOTENAME(year_sold), ', ')
FROM (SELECT DISTINCT year_sold FROM yearly_sales) AS years

-- Build the dynamic SQL
SET @sql = '
SELECT product, ' + @columns + '
FROM (
    SELECT product, year_sold, total_sales
    FROM yearly_sales
) AS source_data
PIVOT (
    SUM(total_sales)
    FOR year_sold IN (' + @columns + ')
) AS pivot_table'

-- Execute the dynamic SQL
EXEC sp_executesql @sql

This approach:

1. Queries the distinct years from your data
2. Builds a comma-separated list of column names
3. Constructs the full PIVOT query as a string
4. Executes the dynamic SQL

Security Warning: Dynamic SQL can be vulnerable to SQL injection attacks. Only use this approach with trusted data sources, and never concatenate user input directly into dynamic SQL strings.

Alternative: Using Conditional Aggregation

For simpler scenarios, you can achieve pivot-like results without PIVOT using conditional aggregation:

SELECT 
    product,
    SUM(CASE WHEN year_sold = 2021 THEN total_sales END) AS [2021],
    SUM(CASE WHEN year_sold = 2022 THEN total_sales END) AS [2022],
    SUM(CASE WHEN year_sold = 2023 THEN total_sales END) AS [2023]
FROM yearly_sales
GROUP BY product;

This approach is often more readable and doesn't require dynamic SQL, but you still need to know the specific years in advance.

Real-World Applications and Patterns

Survey Data Analysis

Imagine you're analyzing customer satisfaction surveys with ratings for different aspects:

CREATE TABLE survey_responses (
    customer_id INT,
    question VARCHAR(100),
    rating INT
);

INSERT INTO survey_responses VALUES
(101, 'Product Quality', 4),
(101, 'Customer Service', 5),
(101, 'Value for Money', 3),
(102, 'Product Quality', 5),
(102, 'Customer Service', 4),
(102, 'Value for Money', 4),
(103, 'Product Quality', 3),
(103, 'Customer Service', 3),
(103, 'Value for Money', 2);

-- Pivot to see all ratings per customer on one row
SELECT customer_id, [Product Quality], [Customer Service], [Value for Money]
FROM survey_responses
PIVOT (
    AVG(rating)
    FOR question IN ([Product Quality], [Customer Service], [Value for Money])
) AS pivot_table
ORDER BY customer_id;

Time Series Analysis

Converting time-based measurements for easier analysis:

CREATE TABLE sensor_readings (
    sensor_location VARCHAR(50),
    reading_date DATE,
    temperature DECIMAL(5,2)
);

-- Sample data for the first week of January
INSERT INTO sensor_readings VALUES
('Factory Floor', '2024-01-01', 22.5),
('Factory Floor', '2024-01-02', 23.1),
('Factory Floor', '2024-01-03', 21.8),
('Warehouse', '2024-01-01', 18.2),
('Warehouse', '2024-01-02', 19.0),
('Warehouse', '2024-01-03', 17.5);

-- Pivot to compare locations side by side
SELECT 
    reading_date,
    [Factory Floor],
    [Warehouse]
FROM sensor_readings
PIVOT (
    AVG(temperature)
    FOR sensor_location IN ([Factory Floor], [Warehouse])
) AS pivot_table
ORDER BY reading_date;

Hands-On Exercise

Let's practice with a realistic scenario. You work for an e-commerce company and need to analyze product performance across different channels.

Setup the Exercise Data

-- Create sample data
CREATE TABLE channel_sales (
    product_name VARCHAR(100),
    sales_channel VARCHAR(50),
    month_year VARCHAR(10),
    revenue DECIMAL(12,2),
    units_sold INT
);

INSERT INTO channel_sales VALUES
('Wireless Headphones', 'Online Store', '2024-01', 45000.00, 300),
('Wireless Headphones', 'Retail Partners', '2024-01', 32000.00, 213),
('Wireless Headphones', 'Mobile App', '2024-01', 28000.00, 187),
('Wireless Headphones', 'Online Store', '2024-02', 52000.00, 347),
('Wireless Headphones', 'Retail Partners', '2024-02', 38000.00, 253),
('Wireless Headphones', 'Mobile App', '2024-02', 31000.00, 207),
('Gaming Mouse', 'Online Store', '2024-01', 22000.00, 440),
('Gaming Mouse', 'Retail Partners', '2024-01', 15000.00, 300),
('Gaming Mouse', 'Mobile App', '2024-01', 8000.00, 160),
('Gaming Mouse', 'Online Store', '2024-02', 25000.00, 500),
('Gaming Mouse', 'Retail Partners', '2024-02', 18000.00, 360),
('Gaming Mouse', 'Mobile App', '2024-02', 12000.00, 240);

Exercise Tasks

Task 1: Create a pivot table showing revenue by product and channel for January 2024 only.

Task 2: Create a pivot table showing units sold by channel and month for Gaming Mouse only.

Task 3: Starting with the results from Task 1, unpivot the data back to long format.

Exercise Solutions

Solution 1: Revenue by product and channel (January 2024)

SELECT product_name, [Online Store], [Retail Partners], [Mobile App]
FROM (
    SELECT product_name, sales_channel, revenue
    FROM channel_sales
    WHERE month_year = '2024-01'
) AS source_data
PIVOT (
    SUM(revenue)
    FOR sales_channel IN ([Online Store], [Retail Partners], [Mobile App])
) AS pivot_table;

Solution 2: Units sold by channel and month (Gaming Mouse)

SELECT sales_channel, [2024-01], [2024-02]
FROM (
    SELECT sales_channel, month_year, units_sold
    FROM channel_sales
    WHERE product_name = 'Gaming Mouse'
) AS source_data
PIVOT (
    SUM(units_sold)
    FOR month_year IN ([2024-01], [2024-02])
) AS pivot_table;

Solution 3: Unpivot the January revenue data

-- First create the pivoted table as a CTE, then unpivot
WITH january_pivot AS (
    SELECT product_name, [Online Store], [Retail Partners], [Mobile App]
    FROM (
        SELECT product_name, sales_channel, revenue
        FROM channel_sales
        WHERE month_year = '2024-01'
    ) AS source_data
    PIVOT (
        SUM(revenue)
        FOR sales_channel IN ([Online Store], [Retail Partners], [Mobile App])
    ) AS pivot_table
)
SELECT product_name, sales_channel, revenue
FROM january_pivot
UNPIVOT (
    revenue FOR sales_channel IN ([Online Store], [Retail Partners], [Mobile App])
) AS unpivot_table;

Common Mistakes & Troubleshooting

Mistake 1: Column Name Mismatches

Problem: Your pivot returns NULL values even though you know the data exists.

-- This will fail if your data contains 'January' but you specify 'JANUARY'
SELECT salesperson, [JANUARY], [FEBRUARY], [MARCH]  -- Wrong case
FROM monthly_sales
PIVOT (
    SUM(sales_amount)
    FOR month_name IN ([JANUARY], [FEBRUARY], [MARCH])  -- Data contains 'January'
) AS pivot_table;

Solution: Always check the exact values in your pivot column:

SELECT DISTINCT month_name FROM monthly_sales;  -- Check actual values first

Mistake 2: Forgetting Aggregate Functions

Problem: PIVOT requires an aggregate function, even if you know there's only one value per combination.

-- This will cause an error
PIVOT (
    sales_amount  -- Missing aggregate function
    FOR month_name IN ([January], [February], [March])
)

Solution: Always include an aggregate function:

PIVOT (
    SUM(sales_amount)  -- Or MAX, MIN, AVG as appropriate
    FOR month_name IN ([January], [February], [March])
)

Mistake 3: Including Extra Columns in Source Query

Problem: Including columns in your source query that aren't part of the pivot operation can cause unexpected results.

-- This might give unexpected results because 'order_date' creates additional groupings
SELECT salesperson, [January], [February], [March]
FROM (
    SELECT salesperson, month_name, sales_amount, order_date  -- Extra column!
    FROM monthly_sales
) AS source_data
PIVOT (
    SUM(sales_amount)
    FOR month_name IN ([January], [February], [March])
) AS pivot_table;

Solution: Only include the columns you need for pivoting:

SELECT salesperson, [January], [February], [March]
FROM (
    SELECT salesperson, month_name, sales_amount  -- Only necessary columns
    FROM monthly_sales
) AS source_data
PIVOT (
    SUM(sales_amount)
    FOR month_name IN ([January], [February], [March])
) AS pivot_table;

Mistake 4: NULL Values in UNPIVOT

Problem: UNPIVOT excludes rows where the value is NULL, which might not be what you want.

Solution: Replace NULLs before unpivoting if you need to preserve them:

SELECT salesperson, quarter, ISNULL(sales_amount, 0) AS sales_amount
FROM (
    SELECT salesperson, 
           ISNULL(Q1_Sales, 0) AS Q1_Sales,
           ISNULL(Q2_Sales, 0) AS Q2_Sales,
           ISNULL(Q3_Sales, 0) AS Q3_Sales,
           ISNULL(Q4_Sales, 0) AS Q4_Sales
    FROM quarterly_sales_wide
) AS prepared_data
UNPIVOT (
    sales_amount FOR quarter IN (Q1_Sales, Q2_Sales, Q3_Sales, Q4_Sales)
) AS unpivot_table;

Database-Specific Variations

While we've focused on SQL Server syntax, other database systems handle pivoting differently:

PostgreSQL: Uses the crosstab function from the tablefunc extension:

-- PostgreSQL approach (requires tablefunc extension)
SELECT * FROM crosstab(
    'SELECT salesperson, month_name, sales_amount FROM monthly_sales ORDER BY 1,2',
    'VALUES (''January''),(''February''),(''March'')'
) AS ct(salesperson TEXT, January NUMERIC, February NUMERIC, March NUMERIC);

MySQL: No built-in PIVOT, but you can use conditional aggregation:

-- MySQL approach
SELECT 
    salesperson,
    SUM(CASE WHEN month_name = 'January' THEN sales_amount END) AS January,
    SUM(CASE WHEN month_name = 'February' THEN sales_amount END) AS February,
    SUM(CASE WHEN month_name = 'March' THEN sales_amount END) AS March
FROM monthly_sales
GROUP BY salesperson;

Oracle: Similar to SQL Server but with slightly different syntax:

-- Oracle approach
SELECT * FROM (
    SELECT salesperson, month_name, sales_amount
    FROM monthly_sales
)
PIVOT (
    SUM(sales_amount)
    FOR month_name IN ('January', 'February', 'March')
);

Summary & Next Steps

Pivoting and unpivoting are essential skills for any data professional working with SQL. You've learned how to:

• Transform row-based data into columnar format using PIVOT
• Convert columnar data back to rows using UNPIVOT
• Handle dynamic scenarios where column names aren't known in advance
• Apply these techniques to real-world scenarios like sales analysis and survey data
• Troubleshoot common mistakes and handle NULL values appropriately

The key insight is that pivoting and unpivoting are fundamentally about changing the shape of your data to match the needs of your analysis or reporting tools. Sometimes you need data in long format for statistical analysis, and sometimes you need it in wide format for dashboards and reports.

Next steps to continue your SQL journey:

1. Window Functions: Learn how to perform complex analytics like running totals, rankings, and moving averages
2. Common Table Expressions (CTEs): Master recursive queries and complex data transformations
3. Performance Optimization: Understand indexing strategies and query optimization for large datasets
4. Advanced Joins: Explore self-joins, cross joins, and complex multi-table operations

The pivot and unpivot operations you've mastered here will serve as building blocks for these more advanced topics, especially when preparing data for analytical functions and complex reporting scenarios.