PIVOT and UNPIVOT Operations

Difficulty: Advanced
Generated on: 2025-07-10 02:30:35
Category: SQL Cheatsheet for Database Development

---

SQL PIVOT and UNPIVOT Cheatsheet (Advanced)

1. Quick Overview

PIVOT: Transforms rows into columns. It aggregates data based on specified row and column values, allowing you to summarize and reshape data for reporting and analysis. Use it when you need to compare values across different categories or dimensions that are currently represented as rows.

UNPIVOT: Transforms columns into rows. It takes columns with similar data and converts them into rows, making the data more normalized and easier to query in certain scenarios. Use it when you need to perform operations on values that are stored in separate columns.

2. Syntax

PIVOT

-- Basic PIVOT Syntax (SQL Server, Oracle)
SELECT
    <non-pivoted column(s)>,
    [column1], [column2], ... [columnN] -- Pivoted columns
FROM
(
    SELECT
        <non-pivoted column(s)>,
        <pivot_column>, -- Column whose values become new columns
        <value_column>  -- Column whose values are aggregated
    FROM
        <source_table>
) AS SourceTable
PIVOT
(
    <aggregation_function>(<value_column>) -- Usually SUM, COUNT, AVG, MIN, MAX
    FOR <pivot_column>
    IN ([column1], [column2], ... [columnN])
) AS PivotTable;

-- Example (SQL Server)
SELECT Product, [Q1], [Q2], [Q3], [Q4]
FROM
(
    SELECT Product, Quarter, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Quarter
    IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

UNPIVOT

-- Basic UNPIVOT Syntax (SQL Server, Oracle)
SELECT
    <non-unpivoted column(s)>,
    <new_column_name_for_column_names>, -- The column that will hold original column names
    <new_column_name_for_values>      -- The column that will hold the values from the original columns
FROM
    <source_table>
UNPIVOT
(
    <new_column_name_for_values>
    FOR <new_column_name_for_column_names>
    IN ([column1], [column2], ... [columnN]) -- Columns to unpivot
) AS UnpivotTable;

-- Example (SQL Server)
SELECT Product, Quarter, SalesAmount
FROM
(
    SELECT Product, Q1, Q2, Q3, Q4
    FROM SalesData
) AS SourceTable
UNPIVOT
(
    SalesAmount
    FOR Quarter
    IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;

3. Common Use Cases

• Reporting and Dashboards: Summarizing sales data by quarter, region, or product category.
• Data Analysis: Comparing performance metrics across different time periods or groups.
• Data Transformation: Converting data from a wide format to a long format (UNPIVOT) or vice versa (PIVOT) for easier analysis.
• Data Warehousing: Reshaping data for star schema or snowflake schema implementations.
• ETL Processes: Transforming data during the Extract, Transform, Load process.
• Financial Reporting: Creating balance sheets or income statements where line items need to be pivoted into columns.

4. Best Practices

• Use NULL Handling: Consider how to handle NULL values in the value column. COALESCE or ISNULL can be used to replace NULL values with a default value.
• Dynamic PIVOT/UNPIVOT: If the list of columns to pivot/unpivot is not known in advance, you can use dynamic SQL to generate the SQL statement. This is more complex but allows for greater flexibility.
• Performance Tuning:
  • Ensure the source table has appropriate indexes.
  • Use WITH clauses (Common Table Expressions - CTEs) to break down complex queries into smaller, more manageable steps. This improves readability and can sometimes aid the query optimizer.
  • Avoid unnecessary subqueries.
  • Consider materializing intermediate results into temporary tables for very large datasets.
• Data Type Considerations: Ensure that the data types of the value column are consistent and appropriate for the aggregation function.
• Security: Avoid SQL injection vulnerabilities when using dynamic SQL. Sanitize input and use parameterized queries.

5. Examples

Example 1: PIVOT Sales Data by Quarter (SQL Server)

-- Sample Data
CREATE TABLE SalesData (
    ProductID INT,
    ProductName VARCHAR(50),
    Quarter VARCHAR(2),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO SalesData (ProductID, ProductName, Quarter, SalesAmount) VALUES
(1, 'Product A', 'Q1', 1000.00),
(1, 'Product A', 'Q2', 1200.00),
(1, 'Product A', 'Q3', 1100.00),
(1, 'Product A', 'Q4', 1300.00),
(2, 'Product B', 'Q1', 800.00),
(2, 'Product B', 'Q2', 900.00),
(2, 'Product B', 'Q3', 850.00),
(2, 'Product B', 'Q4', 950.00);

-- PIVOT Query
SELECT ProductName, [Q1], [Q2], [Q3], [Q4]
FROM
(
    SELECT ProductName, Quarter, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Quarter
    IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

-- Expected Output
-- ProductName | Q1      | Q2      | Q3      | Q4
-- ----------- | ------- | ------- | ------- | -------
-- Product A   | 1000.00 | 1200.00 | 1100.00 | 1300.00
-- Product B   | 800.00  | 900.00  | 850.00  | 950.00

Example 2: UNPIVOT Sales Data (SQL Server)

-- Sample Data
CREATE TABLE QuarterlySales (
    ProductID INT,
    ProductName VARCHAR(50),
    Q1 DECIMAL(10, 2),
    Q2 DECIMAL(10, 2),
    Q3 DECIMAL(10, 2),
    Q4 DECIMAL(10, 2)
);

INSERT INTO QuarterlySales (ProductID, ProductName, Q1, Q2, Q3, Q4) VALUES
(1, 'Product A', 1000.00, 1200.00, 1100.00, 1300.00),
(2, 'Product B', 800.00, 900.00, 850.00, 950.00);

-- UNPIVOT Query
SELECT ProductID, ProductName, Quarter, SalesAmount
FROM
(
    SELECT ProductID, ProductName, Q1, Q2, Q3, Q4
    FROM QuarterlySales
) AS SourceTable
UNPIVOT
(
    SalesAmount
    FOR Quarter
    IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;

-- Expected Output
-- ProductID | ProductName | Quarter | SalesAmount
-- --------- | ----------- | ------- | -----------
-- 1         | Product A   | Q1      | 1000.00
-- 1         | Product A   | Q2      | 1200.00
-- 1         | Product A   | Q3      | 1100.00
-- 1         | Product A   | Q4      | 1300.00
-- 2         | Product B   | Q1      | 800.00
-- 2         | Product B   | Q2      | 900.00
-- 2         | Product B   | Q3      | 850.00
-- 2         | Product B   | Q4      | 950.00

Example 3: Dynamic PIVOT (SQL Server)

-- Dynamic PIVOT - When you don't know the Quarter values in advance
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Quarter)
            FROM SalesData
            ORDER BY Quarter
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT ProductName, ' + @cols + ' from
            (
                SELECT ProductName, Quarter, SalesAmount
                FROM SalesData
           ) x
            PIVOT
            (
                 SUM(SalesAmount)
                FOR Quarter IN (' + @cols + ')
            ) p '

EXECUTE(@query);

Example 4: PIVOT with Multiple Grouping Columns (SQL Server)

-- Sample Data (add Region)
ALTER TABLE SalesData ADD Region VARCHAR(50);

UPDATE SalesData SET Region = 'North' WHERE ProductID = 1;
UPDATE SalesData SET Region = 'South' WHERE ProductID = 2;

-- PIVOT with Region
SELECT Region, ProductName, [Q1], [Q2], [Q3], [Q4]
FROM
(
    SELECT Region, ProductName, Quarter, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Quarter
    IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

6. Common Pitfalls

• Incorrect Aggregation: Using the wrong aggregation function (e.g., AVG when you need SUM).
• Data Type Mismatches: Inconsistent data types in the value column can lead to errors or unexpected results.
• NULL Values: Not handling NULL values appropriately. NULL values are generally ignored by aggregation functions unless explicitly handled with COALESCE or ISNULL.
• Dynamic SQL Injection: Using dynamic SQL without proper sanitization can lead to SQL injection vulnerabilities. Always use parameterized queries when possible.
• Performance Bottlenecks: Complex PIVOT/UNPIVOT operations on large datasets can be slow. Ensure appropriate indexes and consider optimizing the query.
• Forgetting the GROUP BY clause (Implicitly): PIVOT implicitly performs a GROUP BY on the non-pivoted columns. Understanding this is crucial for accurate results.
• Incorrect Column List: Specifying the wrong columns in the IN clause of the PIVOT/UNPIVOT operation.
• Confusing PIVOT and CUBE/ROLLUP: While related, PIVOT transforms rows to columns, while CUBE and ROLLUP generate aggregate subtotals and totals. They serve different purposes.
• Over-Pivoting: Pivoting on columns with too many unique values can result in a wide table with many NULL values, making it difficult to analyze. Consider alternative data modeling approaches.

7. Database Variations

• SQL Server: Supports the PIVOT and UNPIVOT operators directly.
• Oracle: Supports the PIVOT and UNPIVOT operators directly. Syntax is largely the same as SQL Server.
• PostgreSQL: Does not have direct PIVOT and UNPIVOT operators. You can achieve the same results using crosstab() function from the tablefunc extension (for PIVOT) and LATERAL JOIN with UNION ALL (for UNPIVOT).
• MySQL: Does not have direct PIVOT and UNPIVOT operators. You can achieve similar results using conditional aggregation (CASE statements within aggregate functions) and UNION ALL (for UNPIVOT).

PostgreSQL PIVOT Example (using crosstab):

-- Enable the tablefunc extension:
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Example data (same as SQL Server example)
CREATE TABLE SalesData (
    ProductID INT,
    ProductName VARCHAR(50),
    Quarter VARCHAR(2),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO SalesData (ProductID, ProductName, Quarter, SalesAmount) VALUES
(1, 'Product A', 'Q1', 1000.00),
(1, 'Product A', 'Q2', 1200.00),
(1, 'Product A', 'Q3', 1100.00),
(1, 'Product A', 'Q4', 1300.00),
(2, 'Product B', 'Q1', 800.00),
(2, 'Product B', 'Q2', 900.00),
(2, 'Product B', 'Q3', 850.00),
(2, 'Product B', 'Q4', 950.00);

-- PIVOT using crosstab
SELECT *
FROM crosstab(
  'SELECT ProductName, Quarter, SUM(SalesAmount) FROM SalesData GROUP BY ProductName, Quarter ORDER BY ProductName, Quarter',
  'SELECT DISTINCT Quarter FROM SalesData ORDER BY Quarter'
) AS ct (ProductName VARCHAR(50), Q1 DECIMAL(10, 2), Q2 DECIMAL(10, 2), Q3 DECIMAL(10, 2), Q4 DECIMAL(10, 2));

PostgreSQL UNPIVOT Example (using LATERAL JOIN and UNION ALL):

-- Example data (same as SQL Server example)
CREATE TABLE QuarterlySales (
    ProductID INT,
    ProductName VARCHAR(50),
    Q1 DECIMAL(10, 2),
    Q2 DECIMAL(10, 2),
    Q3 DECIMAL(10, 2),
    Q4 DECIMAL(10, 2)
);

INSERT INTO QuarterlySales (ProductID, ProductName, Q1, Q2, Q3, Q4) VALUES
(1, 'Product A', 1000.00, 1200.00, 1100.00, 1300.00),
(2, 'Product B', 800.00, 900.00, 850.00, 950.00);


SELECT
    qs.ProductID,
    qs.ProductName,
    q.Quarter,
    q.SalesAmount
FROM
    QuarterlySales qs
    CROSS JOIN LATERAL (
        VALUES
            ('Q1', qs.Q1),
            ('Q2', qs.Q2),
            ('Q3', qs.Q3),
            ('Q4', qs.Q4)
    ) AS q(Quarter, SalesAmount);

MySQL PIVOT Example (using Conditional Aggregation):

-- Example data (same as SQL Server example)
CREATE TABLE SalesData (
    ProductID INT,
    ProductName VARCHAR(50),
    Quarter VARCHAR(2),
    SalesAmount DECIMAL(10, 2)
);

INSERT INTO SalesData (ProductID, ProductName, Quarter, SalesAmount) VALUES
(1, 'Product A', 'Q1', 1000.00),
(1, 'Product A', 'Q2', 1200.00),
(1, 'Product A', 'Q3', 1100.00),
(1, 'Product A', 'Q4', 1300.00),
(2, 'Product B', 'Q1', 800.00),
(2, 'Product B', 'Q2', 900.00),
(2, 'Product B', 'Q3', 850.00),
(2, 'Product B', 'Q4', 950.00);

-- PIVOT using conditional aggregation
SELECT
    ProductName,
    SUM(CASE WHEN Quarter = 'Q1' THEN SalesAmount ELSE 0 END) AS Q1,
    SUM(CASE WHEN Quarter = 'Q2' THEN SalesAmount ELSE 0 END) AS Q2,
    SUM(CASE WHEN Quarter = 'Q3' THEN SalesAmount ELSE 0 END) AS Q3,
    SUM(CASE WHEN Quarter = 'Q4' THEN SalesAmount ELSE 0 END) AS Q4
FROM
    SalesData
GROUP BY
    ProductName;

MySQL UNPIVOT Example (using UNION ALL):

-- Example data (same as SQL Server example)
CREATE TABLE QuarterlySales (
    ProductID INT,
    ProductName VARCHAR(50),
    Q1 DECIMAL(10, 2),
    Q2 DECIMAL(10, 2),
    Q3 DECIMAL(10, 2),
    Q4 DECIMAL(10, 2)
);

INSERT INTO QuarterlySales (ProductID, ProductName, Q1, Q2, Q3, Q4) VALUES
(1, 'Product A', 1000.00, 1200.00, 1100.00, 1300.00),
(2, 'Product B', 800.00, 900.00, 850.00, 950.00);

-- UNPIVOT using UNION ALL
SELECT ProductID, ProductName, 'Q1' AS Quarter, Q1 AS SalesAmount FROM QuarterlySales
UNION ALL
SELECT ProductID, ProductName, 'Q2' AS Quarter, Q2 AS SalesAmount FROM QuarterlySales
UNION ALL
SELECT ProductID, ProductName, 'Q3' AS Quarter, Q3 AS SalesAmount FROM QuarterlySales
UNION ALL
SELECT ProductID, ProductName, 'Q4' AS Quarter, Q4 AS SalesAmount FROM QuarterlySales;

This cheatsheet provides a comprehensive guide to using PIVOT and UNPIVOT operations in SQL. Remember to adapt the examples and best practices to your specific database system and data requirements. Always prioritize data integrity, performance, and security when implementing these techniques.