Dynamic Pivoting

Dynamic pivoting is a technique used when the columns to be pivoted are not known in advance. It enables the transformation of rows into columns dynamically based on the data. This is particularly useful for reports where the pivot columns (e.g., years, months, categories) are subject to change or grow over time.

How Does Dynamic Pivoting Work?

Dynamic pivoting typically involves:

1. Identifying Unique Values for Pivot Columns
   Extract unique values from the column to be pivoted (e.g., years or categories).
2. Building the Pivot Query Dynamically
   Construct the pivot SQL query as a string, incorporating the dynamically identified column names.
3. Executing the Query
   Use dynamic SQL execution capabilities of the database to run the generated query.

Internal Workflow

Step 1: Extract Unique Values

Identify unique values from the column that will become pivoted headers using a query like DISTINCT.

Step 2: Generate a Column List

Format these values as column names using a string-building function, such as:

• STRING_AGG() in SQL Server and PostgreSQL.
• GROUP_CONCAT() in MySQL.

Step 3: Build the SQL Query

Compose a pivot query dynamically, including the generated column list in the FOR clause (for databases with a PIVOT operator) or in CASE statements (for databases without).

Step 4: Execute the Query

Use database-specific dynamic SQL execution methods:

• EXEC() or sp_executesql in SQL Server.
• PL/pgSQL or other procedural languages in PostgreSQL.
• PREPARE and EXECUTE in MySQL.

Dynamic Pivot Examples

Dynamic Pivoting in SQL Server

Scenario:
You have a Sales table, and new years are continuously added. You want to create a report dynamically summarizing sales by region and year.

Input Table:

Query for Dynamic Pivoting:

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

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

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

-- Step 3: Execute the query
EXEC sp_executesql @query;

How It Works Internally:

1. STRING_AGG: Creates a comma-separated list of years like [2023], [2024].
2. Dynamic Query: Constructs the pivot query using this list.
3. Execution: The EXEC sp_executesql command runs the dynamically generated query.

Output Table:

Dynamic Pivoting in MySQL

MySQL doesn’t have a PIVOT operator, but you can dynamically generate conditional aggregation queries.

Query:

SET @sql = NULL;

-- Step 1: Generate the column list dynamically
SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE WHEN Year = ', Year, ' THEN Sales ELSE 0 END) AS `', Year, '`'
)) INTO @sql
FROM Sales;

-- Step 2: Build the dynamic query
SET @sql = CONCAT('SELECT Region, ', @sql, ' 
FROM Sales
GROUP BY Region');

-- Step 3: Execute the query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

How It Works Internally:

1. GROUP_CONCAT: Creates conditional aggregation statements for each year.
2. Dynamic Query: Combines these conditions into a full SQL query.
3. Execution: The PREPARE and EXECUTE commands run the generated query.

Dynamic Pivoting in PostgreSQL

PostgreSQL doesn’t support dynamic pivoting natively, so you use procedural SQL (PL/pgSQL) or generate the query externally.

Query in PL/pgSQL:

DO $$
DECLARE
    cols TEXT;
    query TEXT;
BEGIN
    -- Step 1: Generate column list dynamically
    SELECT string_agg(DISTINCT format('"%s"', Year), ', ')
    INTO cols
    FROM Sales;

    -- Step 2: Build the query
    query := format('
        SELECT Region, %s
        FROM (
            SELECT Year, Region, Sales
            FROM Sales
        ) src
        PIVOT (
            SUM(Sales)
            FOR Year IN (%s)
        ) pvt;',
        cols, cols);

    -- Step 3: Execute the query
    EXECUTE query;
END $$;

Dynamic Pivoting in Oracle

Oracle supports dynamic pivoting using PL/SQL.

Query:

DECLARE
    cols VARCHAR2(4000);
    sql_query VARCHAR2(4000);
BEGIN
    -- Step 1: Generate the column list
    SELECT LISTAGG('''' || Year || ''' AS "' || Year || '"', ', ') WITHIN GROUP (ORDER BY Year)
    INTO cols
    FROM (SELECT DISTINCT Year FROM Sales);

    -- Step 2: Create the pivot query
    sql_query := '
        SELECT * FROM (
            SELECT Year, Region, Sales
            FROM Sales
        ) src
        PIVOT (
            SUM(Sales)
            FOR Year IN (' || cols || ')
        )';

    -- Step 3: Execute the dynamic query
    EXECUTE IMMEDIATE sql_query;
END;

Advantages of Dynamic Pivoting

1. Flexibility: Adapts to changes in data (e.g., new years or categories).
2. Automation: Eliminates manual adjustments to the pivot query for new data.
3. Scalability: Handles large, dynamic datasets effectively.

Challenges with Dynamic Pivoting

1. Complexity: Requires understanding of dynamic SQL and string manipulation.
2. Execution Overhead: Building and executing queries dynamically can add slight overhead.
3. SQL Injection Risk: Ensure user inputs are sanitized when constructing dynamic queries.

Key Use Cases for Dynamic Pivoting

1. Financial Reports: Generate revenue or expense summaries by dynamically growing time periods.
2. Sales Reports: Add new regions, products, or years to the report without altering the SQL.
3. Custom Dashboards: Adapt SQL queries to accommodate user-defined filters or preferences.