Pivoting Data in SQL

The goal is to pivot this data so that years become columns.

Query Using CASE Statements:

SELECT region,
                           SUM(CASE WHEN year = 2022 THEN total_sales ELSE 0 END) AS sales_2022,
                           SUM(CASE WHEN year = 2023 THEN total_sales ELSE 0 END) AS sales_2023
                    FROM sales
                    GROUP BY region;

Output:

This method works on all SQL databases, including MySQL, PostgreSQL, SQLite, and Oracle.

2. Using the PIVOT Operator (SQL Server Only)

Some databases, like SQL Server, provide a built-in PIVOT function to simplify pivoting.

Example: Pivoting Sales by Year in SQL Server

SELECT * FROM (
                    SELECT year, region, total_sales FROM sales
                ) AS SourceTable
                PIVOT (
                    SUM(total_sales) FOR year IN ([2022], [2023])
                ) AS PivotTable;

Output:

The PIVOT function simplifies queries but is only available in SQL Server.

3. Using Dynamic SQL for Dynamic Pivoting

If the column values (years, categories, etc.) are unknown beforehand, dynamic pivoting is required. This requires dynamic SQL or JSON functions, depending on the database.

Dynamic SQL Pivot Example (MySQL, PostgreSQL, SQL Server)

SET @sql = NULL;
            SELECT GROUP_CONCAT(DISTINCT CONCAT(
                'SUM(CASE WHEN year = ', year, ' THEN total_sales ELSE 0 END) AS sales_', year
            )) INTO @sql FROM sales;
            SET @sql = CONCAT('SELECT region, ', @sql, ' FROM sales GROUP BY region;');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

This approach is flexible and works for any number of years dynamically.

Real-World Use Case: Monthly Sales Report

Consider a sales table with revenue categorized by month:

The goal is to pivot months into columns for better readability.

Query Using CASE Statements:

SELECT category,
               SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS Jan_Revenue,
               SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS Feb_Revenue
        FROM sales
        GROUP BY category;

Output:

This format makes sales data easier to compare across months.

Pivoting vs. Normal Queries

Pivoting is useful when comparing multiple categories across a time period, such as sales, performance, and expenses.

Conclusion

Pivoting in SQL transforms row-based data into column-based summaries, making reports easier to read.

• CASE + Aggregation works in all SQL databases.
• The PIVOT function is available in SQL Server for a simpler approach.
• Dynamic SQL allows pivoting for unknown columns.

Choosing the right method depends on database support and reporting needs.