Pivoting

What is Pivoting in SQL?

Pivoting is a data transformation technique in SQL where rows are converted into columns. This transformation allows you to display data in a tabular format that’s more suitable for analysis, reporting, or dashboards. The operation involves taking unique values from a column and turning them into column headers, with aggregated data filling the cells of the new table.

Why Use Pivoting?

1. Readability
   Pivoting makes raw data easier to interpret. For example, sales data for multiple years can be transformed into a table where each year’s sales appear as a separate column, making comparisons simple.
2. Aggregation
   Pivoting allows for aggregation of data (e.g., totals, averages) within specific groups, making it invaluable for generating summary reports.
3. Comparison
   By displaying data side-by-side, pivoting makes it easy to compare values across categories, time periods, or groups.

Example Scenario:
Suppose you manage sales data with columns for years, regions, and sales amounts. Instead of having multiple rows for each year, you can pivot the data to display years as columns and regions as rows.

How Pivoting Works in SQL

1. Identify the Columns to Pivot
   Choose a column whose unique values will become new column headers (e.g., “Year” in sales data).
2. Apply Aggregation
   Use an aggregate function like SUM, COUNT, or AVG to compute the values for the pivoted columns.
3. Group the Data
   Use GROUP BY to organize data by the remaining columns (e.g., “Region” in sales data).

Pivoting in Different Relational Databases

1. SQL Server

SQL Server provides the PIVOT operator, which simplifies the process of creating pivoted tables. The PIVOT operator automates column transformation and aggregation.

Example: Sales Data

Input Table:

Desired Output:

Query Using PIVOT:

SELECT *
FROM (
    SELECT Year, Region, Sales
    FROM Sales
) src
PIVOT (
    SUM(Sales)
    FOR Year IN ([2023], [2024])
) pvt;

Explanation:

• The subquery (src) selects the raw data.
• The PIVOT operator creates new columns (2023 and 2024) and applies the SUM aggregation to fill the values.

2. PostgreSQL

PostgreSQL does not have a built-in PIVOT operator. Instead, it uses conditional aggregation to pivot data.

Example: Sales Data

Query Using Conditional Aggregation:

SELECT
    Region,
    SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS "2023 Sales",
    SUM(CASE WHEN Year = 2024 THEN Sales ELSE 0 END) AS "2024 Sales"
FROM Sales
GROUP BY Region;

Explanation:

• The CASE statement checks the value of the Year column.
• The SUM function aggregates sales for each year into separate columns.
• The GROUP BY clause organizes the results by Region.

3. MySQL

Similar to PostgreSQL, MySQL also relies on conditional aggregation.

Query:

SELECT
    Region,
    SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS "2023 Sales",
    SUM(CASE WHEN Year = 2024 THEN Sales ELSE 0 END) AS "2024 Sales"
FROM Sales
GROUP BY Region;

The structure and logic are the same as in PostgreSQL. MySQL uses the CASE statement to implement pivoting.

4. Oracle

Oracle provides a built-in PIVOT operator, similar to SQL Server.

Query Using PIVOT:

SELECT *
FROM (
    SELECT Year, Region, Sales
    FROM Sales
) src
PIVOT (
    SUM(Sales)
    FOR Year IN (2023 AS "2023 Sales", 2024 AS "2024 Sales")
);

Explanation:

• The subquery (src) provides the source data.
• The PIVOT operator transforms the rows into columns (2023 and 2024), with SUM used to aggregate the sales data.

5. SQLite

SQLite does not support a PIVOT operator, so you must use conditional aggregation.

Query:

SELECT
    Region,
    SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS "2023 Sales",
    SUM(CASE WHEN Year = 2024 THEN Sales ELSE 0 END) AS "2024 Sales"
FROM Sales
GROUP BY Region;

Dynamic Pivoting

Dynamic pivoting is used when the column names for the pivoted table are not known in advance. This is common when new data (e.g., years or categories) is added dynamically.

Dynamic Pivot in SQL Server

DECLARE @columns NVARCHAR(MAX);
DECLARE @query NVARCHAR(MAX);

-- Generate column list dynamically
SELECT @columns = STRING_AGG(QUOTENAME(Year), ',')
FROM (SELECT DISTINCT Year FROM Sales) AS Years;

-- Create dynamic SQL query
SET @query = '
SELECT *
FROM (
    SELECT Year, Region, Sales
    FROM Sales
) src
PIVOT (
    SUM(Sales)
    FOR Year IN (' + @columns + ')
) pvt;';

-- Execute the query
EXEC sp_executesql @query;

Use Cases for Pivoting

1. Financial Reporting
   Aggregate revenues or expenses by month or quarter for each department.
2. Product Sales Reports
   Summarize product sales across regions or years.
3. Employee Performance
   Show employee KPIs across different periods or projects.
4. Inventory Analysis
   Display stock levels by category and warehouse location.

Comparison of Pivoting Methods