Dynamic SQL Pivot: How to Pivot on Unknown Columns Safely

Introduction

People usually ask for a dynamic pivot when they have a table of keys and values, but the set of keys is not stable enough to hard-code into the query. That is a real problem. It is also the reason the solution gets awkward quickly: a normal SQL statement expects its output columns to be known up front.

So when someone says “I want SQL to pivot this automatically,” what they usually mean is “I need SQL to inspect the data, decide which columns should exist, build a second query, and run that query safely.” That is not impossible, but it is dynamic SQL, not a magical version of PIVOT that removes all tradeoffs.

The best version of this article is not just a recipe. It is a decision guide: when dynamic pivoting is justified, when conditional aggregation is enough, and when the pivot should happen somewhere other than the database.

If You Only Remember Four Rules

• A dynamic pivot is usually a query that generates another query.
• If the set of output columns is stable, static conditional aggregation is simpler and safer.
• Quote generated identifiers carefully. Never let unchecked input become executable SQL.
• If the consumer can accept tall data, keeping the result tall is often the better design.

Why Dynamic Pivot Is Hard in SQL

SQL works best when the output schema is known at parse time. Dynamic pivoting breaks that assumption because the column list depends on the data you find at runtime. That means the engine cannot simply infer the columns inside one ordinary SELECT and return a result shape that no caller expected.

In practice, dynamic pivoting nearly always means two steps:

1. Read the distinct values that should become columns.
2. Generate a second statement that turns those values into identifiers and executes the final query.

Once you accept that model, the problem stops feeling mysterious. It becomes a normal query-generation problem with familiar concerns: safety, determinism, and output contracts.

First Ask Whether the Pivot Belongs in the Database

Not every wide report should be built as dynamic SQL. If the pivot only exists so a dashboard, spreadsheet, or export looks familiar, the application layer or reporting layer may be a better home for it. The database is strongest when it stores and transforms data in a stable shape. A changing column layout is usually the opposite of stable.

Use a database-side dynamic pivot when the downstream contract truly expects wide columns and the data-driven column set is part of the requirement, not just a presentation preference.

Static Conditional Aggregation Is Usually the Right Starting Point

Before you build dynamic SQL, test whether the category list is actually stable enough to write explicitly. Conditional aggregation is portable, reviewable, and easy to benchmark.

SELECT customer_id,
       SUM(CASE WHEN channel = 'email' THEN revenue ELSE 0 END) AS email_revenue,
       SUM(CASE WHEN channel = 'paid' THEN revenue ELSE 0 END) AS paid_revenue,
       SUM(CASE WHEN channel = 'organic' THEN revenue ELSE 0 END) AS organic_revenue
FROM marketing_events
GROUP BY customer_id;

If the set of channels changes once a year instead of every day, this is usually the best answer. It is also easier to combine with other patterns such as pre-aggregation or CTE staging. For that side of the design choice, see SQL CTE vs Subquery vs Temp Table.

SQL Server: Dynamic PIVOT with QUOTENAME

SQL Server has a native PIVOT operator, but it still needs the final column list. The standard pattern is to generate that list dynamically and protect each column identifier with QUOTENAME.

DECLARE @cols nvarchar(max);
DECLARE @sql  nvarchar(max);

SELECT @cols = STRING_AGG(QUOTENAME(attribute_key), ',')
FROM (
    SELECT DISTINCT attribute_key
    FROM product_attributes
) AS keys;

SET @sql = N'
SELECT product_id, ' + @cols + N'
FROM (
    SELECT product_id, attribute_key, attribute_value
    FROM product_attributes
) AS src
PIVOT (
    MAX(attribute_value)
    FOR attribute_key IN (' + @cols + N')
) AS p
ORDER BY product_id;';

EXEC sp_executesql @sql;

QUOTENAME is not optional polish. It is the difference between a workable pattern and a fragile or unsafe one.

PostgreSQL: Generate Conditional Aggregates Instead of Chasing a Magic PIVOT

PostgreSQL does not provide a built-in dynamic PIVOT shortcut that eliminates output-column definition. That is why many Postgres solutions generate conditional aggregate expressions instead.

DO $$
DECLARE
    cols text;
    sql_text text;
BEGIN
    SELECT string_agg(
        format(
            'MAX(CASE WHEN attribute_key = %L THEN attribute_value END) AS %I',
            attribute_key,
            attribute_key
        ),
        ', '
    )
    INTO cols
    FROM (
        SELECT DISTINCT attribute_key
        FROM product_attributes
        ORDER BY attribute_key
    ) AS keys;

    sql_text := format(
        'SELECT product_id, %s
         FROM product_attributes
         GROUP BY product_id
         ORDER BY product_id',
        cols
    );

    EXECUTE sql_text;
END $$;

If your Postgres use case is really a stable static pivot, an older static guide such as Mastering Pivot Tables in PostgreSQL for Interviews may still help. The dynamic case is different because the column list is not fixed.

Warehouses: Use Dynamic SQL, but Keep the Output Contract Clear

BigQuery, Snowflake, and similar warehouses usually solve this with scripting features such as EXECUTE IMMEDIATE. The core design rule does not change: discover the keys first, generate from a strict template, and be honest about whether the caller can handle a changing schema.

This is also where operational debugging matters. If a generated query suddenly becomes slow because the category list exploded, the next step is not guessing. It is reading the generated SQL, checking row counts, and tracing the plan. That debugging workflow is exactly the kind of work described in How to Read SQL EXPLAIN Plans Without Guessing.

A Practical Decision Table

Security and Maintainability Checklist

• Quote identifiers, not just values.
• Never let raw end-user text become table names or SQL fragments.
• Sort the discovered keys explicitly so the output order is deterministic.
• Log or print the generated query during debugging.
• Be clear about which layer owns the final wide shape.

If you cannot satisfy those constraints, it is usually a sign that the pivot should move out of dynamic SQL.

Related Reading

• SQL CTE vs Subquery vs Temp Table
• How to Read SQL EXPLAIN Plans Without Guessing
• Mastering SQL Pivot: Convert Rows to Columns Efficiently

FAQ

Why can’t SQL just create the pivot columns automatically?

Because a normal SQL statement expects its output columns to be known before execution results are consumed. A dynamic pivot changes that schema based on the data, which usually requires generating a second query.

Is conditional aggregation better than PIVOT?

Often yes. It is more portable and usually easier to debug. Native PIVOT syntax can still be useful when the column list is known and the dialect supports it cleanly.

When is a dynamic pivot a bad idea?

When the wide shape is only for convenience, when the consumer cannot tolerate schema changes, or when the generated SQL becomes hard to reason about safely. In those cases, keep the data tall and pivot later.