Dynamic SQL Pivot: PostgreSQL Export with Safe Column Rules

Dynamic columns need an explicit output contract

A dynamic pivot turns distinct values into output column names. That is useful for an exploratory export, but it changes the result schema whenever a new category appears. Start by deciding who consumes those changing columns. This guide provides a complete PostgreSQL 16 example that actually returns the pivoted rows to the psql client, including safe quoting and bounded column discovery.

Four decisions before generating SQL

Define the output grain, choose the aggregate or winner rule, distinguish identifiers from values, and set limits on the discovered columns. A product-and-attribute table can contain several values per attribute; MAX is not a universal “latest value” rule. Here a primary key guarantees one value per product and attribute, so the aggregate only reshapes known-unique data.

The column list is part of the statement

A normal query has a result shape determined when it is parsed. A bound value parameter can select a color, but cannot stand in for a column name or a whole SELECT list. A dynamic pivot therefore has two stages: discover the allowed names, then construct and execute a statement containing them. Changes between those stages can make the result inconsistent. This fixture uses a session-local temporary table so another session cannot alter its input.

Choose a stable shape when the consumer needs one

For a dashboard expecting color and size, a fixed column list is easier to validate. For an API, long-form rows such as product_id, attribute_key, attribute_value may be a better contract. A spreadsheet export can reasonably accept a changing header if the export includes that header and clearly identifies missing values. Do not make downstream code guess which column position means which attribute.

A static pivot is a useful baseline

WITH attributes(product_id,attribute_key,attribute_value) AS (
 VALUES (1,'color','blue'),(1,'size"cm','M'),(2,'color','red')
)
SELECT product_id,
 MAX(attribute_value) FILTER (WHERE attribute_key='color') AS color,
 MAX(attribute_value) FILTER (WHERE attribute_key='size"cm') AS "size""cm"
FROM attributes GROUP BY product_id ORDER BY product_id;

product_id | color | size"cm
1 | blue | M
2 | red | NULL

The doubled double quote in the identifier is SQL syntax; the visible output header contains one quote. Product 2 has no size row, so the result is NULL. If an attribute row exists with a NULL value, this layout cannot distinguish it from absence; add presence columns or retain long-form data if that distinction matters.

Return the dynamic result through psql

Run the entire following script in psql connected to a disposable learning database. The backslash commands belong to psql, not to the SQL language. A GUI query editor or application driver will generally need its own fetch-generated-SQL-and-execute step. The script creates only temporary data and rolls it back.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE pivot_demo (
 product_id integer, attribute_key text NOT NULL, attribute_value text,
 PRIMARY KEY (product_id,attribute_key)
) ON COMMIT DROP;
INSERT INTO pivot_demo VALUES
 (1,'color','blue'),(1,'size"cm','M'),(2,'color','red');
DO $$
BEGIN
 IF (SELECT COUNT(DISTINCT attribute_key) FROM pg_temp.pivot_demo) NOT BETWEEN 1 AND 20 THEN
   RAISE EXCEPTION 'Expected between 1 and 20 attribute columns';
 END IF;
 IF EXISTS (SELECT 1 FROM pg_temp.pivot_demo
   WHERE attribute_key='' OR attribute_key='product_id'
      OR OCTET_LENGTH(attribute_key)>current_setting('max_identifier_length')::int) THEN
   RAISE EXCEPTION 'Empty, conflicting, or over-length output name';
 END IF;
END $$;
SELECT FORMAT(
 'SELECT product_id, %s FROM pg_temp.pivot_demo GROUP BY product_id ORDER BY product_id',
 STRING_AGG(FORMAT(
   'MAX(attribute_value) FILTER (WHERE attribute_key = %L) AS %I',
   attribute_key, attribute_key), ', ' ORDER BY attribute_key COLLATE "C")
)
FROM (SELECT DISTINCT attribute_key FROM pg_temp.pivot_demo) keys
\gexec
ROLLBACK;

product_id | color | size"cm
1 | blue | M
2 | red | NULL

The generating SELECT deliberately has no semicolon before \gexec. That command sends each returned SQL string back to the server and displays the resulting query output. A PostgreSQL DO block executing a SELECT does not, by itself, deliver that SELECT result as a client result set. The DO block here is used only for validation. See the official psql reference.

FORMAT uses %L for the comparison literal and %I for the output identifier; they serve different roles. The table name is fixed. Sorting the distinct keys with the C collation makes the column order explicit. The guard rejects zero columns, more than 20 columns, a collision with product_id, empty names, and names longer than the server identifier limit. These are intentional export rules, not universal database limits. See FORMAT quoting rules.

Porting to SQL Server requires its own implementation

SQL Server has PIVOT and QUOTENAME, but this PostgreSQL script is not Transact-SQL. QUOTENAME escapes identifiers and accepts inputs of at most 128 characters; longer inputs return NULL. It does not replace bound parameters for values. A SQL Server implementation must separately handle an empty category set, duplicate values, deterministic column ordering, identifier limits, and returning the result to its client. See Microsoft’s QUOTENAME reference.

Check the execution environment

Warehouse scripting, stored procedures, and database clients differ in how they expose dynamically generated results. Verify the exact engine and client together. A generated string that looks correct is only half the implementation; the consumer must receive the expected column names, row values, and empty-input behavior.

Choose by consumer

• Known dashboard columns: fixed conditional aggregation.
• General API or unbounded categories: long-form rows.
• Small exploratory export: bounded dynamic pivot with a visible header.
• Repeated production report: versioned schema and explicit change handling.

Test the shape as well as the values

Test a quote in a key, a missing attribute, no input rows, duplicate product-attribute pairs, and an over-length name. The primary key rejects duplicate pairs instead of silently choosing one. For a shared production source, use an appropriate consistent snapshot or materialize the input first. Apply an allowlist and access controls to any externally chosen dataset; quoting alone does not authorize a table or a column.

Related practice

Review deterministic duplicate handling before pivoting data that lacks a unique product-attribute key.

FAQ

Can the output contain arbitrary attributes? Only within a defined export contract and column budget. Very wide results are often less useful than long-form data. Why is MAX acceptable here? The primary key permits at most one input value for each output cell.