PIVOT, UNPIVOT, GROUPING SETS, ROLLUP and CUBE

Reports and machine-learning features often need data in a different shape from how it is stored: one column per month instead of one row per month, or one row per metric instead of one column per metric. Reports also need subtotals and grand totals alongside the detail. This lesson covers both reshaping (pivot and unpivot, including the dynamic-column problem) and multi-level aggregation with GROUPING SETS, ROLLUP and CUBE. PostgreSQL has no PIVOT keyword, so the portable techniques come first, followed by the PIVOT operators of DuckDB, Snowflake and SQL Server.

Sample data

Monthly revenue by region and sales channel, already aggregated from orders. EU had no app sales in February.

CREATE TABLE monthly_sales (
  sales_month TEXT NOT NULL,       -- 'YYYY-MM'
  region      TEXT NOT NULL,
  channel     TEXT NOT NULL,
  revenue     NUMERIC(10, 2) NOT NULL
);

INSERT INTO monthly_sales VALUES
  ('2025-01', 'EU', 'web', 1200), ('2025-01', 'EU', 'app',  300),
  ('2025-01', 'US', 'web', 2000), ('2025-01', 'US', 'app',  800),
  ('2025-02', 'EU', 'web', 1100),
  ('2025-02', 'US', 'web', 2100), ('2025-02', 'US', 'app',  950),
  ('2025-03', 'EU', 'web', 1300), ('2025-03', 'EU', 'app',  400),
  ('2025-03', 'US', 'web', 2300), ('2025-03', 'US', 'app', 1000);

PIVOT: turning rows into columns

Pivoting turns the distinct values of one column (here, months) into separate output columns, aggregating a measure into each cell. The result is a wide table, the shape of a spreadsheet report.

Portable pivot with conditional aggregation

Every engine can pivot with GROUP BY and one CASE (or FILTER) per output column:

SELECT region,
       SUM(CASE WHEN sales_month = '2025-01' THEN revenue END) AS jan,
       SUM(CASE WHEN sales_month = '2025-02' THEN revenue END) AS feb,
       SUM(CASE WHEN sales_month = '2025-03' THEN revenue END) AS mar,
       SUM(revenue)                                            AS q1_total
FROM monthly_sales
GROUP BY region
ORDER BY region;

• The GROUP BY columns become the rows; each CASE defines one column.
• With no ELSE, a cell with no data is NULL; with ELSE 0 it is 0. NULL means “no data” and 0 means “zero sales”, so pick deliberately.
• You can add extra columns (q1_total) and several measures in the same pass, which the PIVOT operator makes awkward.

PostgreSQL’s FILTER clause reads more cleanly: SUM(revenue) FILTER (WHERE sales_month = '2025-01') AS jan.

PostgreSQL crosstab

The tablefunc extension provides crosstab, which pivots the output of a query. You must declare the output columns and their types, and supply the category list so that missing cells land in the right column:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$SELECT region, sales_month, SUM(revenue) FROM monthly_sales GROUP BY 1, 2 ORDER BY 1, 2$$,
  $$VALUES ('2025-01'), ('2025-02'), ('2025-03')$$
) AS ct(region TEXT, jan NUMERIC, feb NUMERIC, mar NUMERIC);

Conditional aggregation is usually easier to read and needs no extension, so crosstab is mostly met in existing code.

The PIVOT operator

DuckDB, Snowflake, SQL Server, Oracle, BigQuery and Databricks have a PIVOT operator. In DuckDB (the standard-style syntax also works in Snowflake):

CREATE TABLE monthly_sales (sales_month TEXT, region TEXT, channel TEXT, revenue DECIMAL(10, 2));
INSERT INTO monthly_sales VALUES
  ('2025-01', 'EU', 'web', 1200), ('2025-01', 'EU', 'app',  300),
  ('2025-01', 'US', 'web', 2000), ('2025-01', 'US', 'app',  800),
  ('2025-02', 'EU', 'web', 1100),
  ('2025-02', 'US', 'web', 2100), ('2025-02', 'US', 'app',  950),
  ('2025-03', 'EU', 'web', 1300), ('2025-03', 'EU', 'app',  400),
  ('2025-03', 'US', 'web', 2300), ('2025-03', 'US', 'app', 1000);

SELECT *
FROM (SELECT region, sales_month, revenue FROM monthly_sales)
PIVOT (SUM(revenue) FOR sales_month IN ('2025-01' AS jan, '2025-02' AS feb, '2025-03' AS mar))
ORDER BY region;

The important rule: every column of the input that is not the pivot column or the aggregated value becomes an implicit grouping column. That is why the input is narrowed to region, sales_month, revenue first. Feed it the whole table and channel would become a grouping column too, giving one row per region and channel.

The SQL Server form needs an alias for the source and bracketed column names:

-- SQL Server (T-SQL)
SELECT region, [2025-01] AS jan, [2025-02] AS feb, [2025-03] AS mar
FROM (SELECT region, sales_month, revenue FROM monthly_sales) AS src
PIVOT (SUM(revenue) FOR sales_month IN ([2025-01], [2025-02], [2025-03])) AS p
ORDER BY region;

Pitfalls

• Extra columns in the pivot input silently becoming grouping columns.
• Pivoting on values that are not known to be complete: a month missing from the IN list is simply dropped, with no error.
• Mixing NULL (no data) with 0 (zero sales) in the output without deciding which you want.
• Pivoting inside a pipeline. Wide tables are good for presentation but awkward to filter, join and extend. Store data long and pivot at the reporting layer.

In interviews

“Show revenue per region with a column per month” is a common prompt. Write the SUM(CASE ...) version first, because it works everywhere, then mention the PIVOT operator in the interviewer’s engine and the implicit-grouping trap. Saying that pivots belong at the presentation layer shows design sense.

UNPIVOT: turning columns into rows

Unpivoting is the reverse: it turns several columns into rows of (name, value) pairs. You need it when a source arrives wide, such as a spreadsheet with a column per month, or a sensor table with a column per metric, and you want the long, tidy shape that SQL aggregates easily.

Here is a wide table, as a finance team might upload it:

CREATE TABLE region_targets (region TEXT, jan NUMERIC, feb NUMERIC, mar NUMERIC);
INSERT INTO region_targets VALUES ('EU', 1400, 1400, NULL), ('US', 2700, 3000, 3200);

EU has not set a March target yet.

Portable unpivot with LATERAL VALUES

PostgreSQL has no UNPIVOT keyword. A LATERAL list of VALUES does the job, and works in any engine with lateral joins:

SELECT t.region, v.month_name, v.target
FROM region_targets t
CROSS JOIN LATERAL (
  VALUES ('jan', t.jan), ('feb', t.feb), ('mar', t.mar)
) AS v(month_name, target)
ORDER BY t.region, v.month_name;

Each input row produces one output row per listed column. Note that EU’s missing March target is kept as a NULL row; add WHERE v.target IS NOT NULL to drop it. The older portable alternative is one SELECT per column glued together with UNION ALL, which scans the table once per column.

The UNPIVOT operator

In DuckDB:

CREATE TABLE region_targets (region TEXT, jan DECIMAL(10, 2), feb DECIMAL(10, 2), mar DECIMAL(10, 2));
INSERT INTO region_targets VALUES ('EU', 1400, 1400, NULL), ('US', 2700, 3000, 3200);

SELECT *
FROM region_targets
UNPIVOT (target FOR month_name IN (jan, feb, mar))
ORDER BY region, month_name;

The UNPIVOT operator drops NULL values by default, so EU’s March row is gone. DuckDB, Snowflake and BigQuery accept UNPIVOT INCLUDE NULLS (...) to keep them; SQL Server’s UNPIVOT always drops them, so use CROSS APPLY (VALUES ...) there if you need the NULL rows. DuckDB also accepts a shorter form, UNPIVOT region_targets ON jan, feb, mar INTO NAME month_name VALUE target, and ON COLUMNS(* EXCLUDE (region)) to unpivot every other column.

Pitfalls

• Losing NULL rows unintentionally with the UNPIVOT operator, or keeping them unintentionally with LATERAL VALUES.
• Unpivoting columns of different types (a number and a text column): the value column needs one type, so cast first.
• Hard-coding month names as text when the target needs a date: map 'jan' to DATE '2025-01-01' in the VALUES list instead.

In interviews

Unpivot comes up as “this table has a column per quarter; produce one row per quarter” or as a data-modelling question about wide versus long tables. Show the UNION ALL or LATERAL VALUES approach, mention the operator in Snowflake, SQL Server or DuckDB, and state how NULL cells are handled.

Pivoting dynamic columns

Every pivot so far listed its output columns explicitly. In real data the values change: a new month or a new product category appears. SQL needs to know a query’s columns before it runs, so a truly dynamic pivot requires either an engine feature that discovers them or dynamic SQL: a first query that builds the second query’s text.

Engines that discover the columns

DuckDB’s simplified PIVOT finds the distinct values itself when you leave out IN:

PIVOT monthly_sales ON sales_month USING SUM(revenue) GROUP BY region ORDER BY region;

Snowflake supports dynamic pivots with ANY or a subquery in the IN clause:

-- Snowflake
SELECT *
FROM (SELECT region, sales_month, revenue FROM monthly_sales)
  PIVOT (SUM(revenue) FOR sales_month IN (ANY ORDER BY sales_month))
ORDER BY region;

Snowflake documents some limits: a view built on a dynamic pivot can break when the data changes the output columns, and dynamic pivot is not supported inside a stored procedure or UDF body.

Dynamic SQL everywhere else

In SQL Server, PostgreSQL, BigQuery (EXECUTE IMMEDIATE) and others, generate the column list from the data, then run the generated statement. In PostgreSQL you can build the text with string_agg:

SELECT 'SELECT region, '
       || string_agg(
            format('SUM(revenue) FILTER (WHERE sales_month = %L) AS %I', sales_month, 'm_' || replace(sales_month, '-', '_')),
            ', ' ORDER BY sales_month)
       || ' FROM monthly_sales GROUP BY region ORDER BY region' AS generated_sql
FROM (SELECT DISTINCT sales_month FROM monthly_sales) m;

In psql you can run the result directly with \gexec; in application or orchestration code you execute the string; in a PL/pgSQL function you use EXECUTE. format() with %L (literal) and %I (identifier) quotes values safely. In SQL Server, the equivalent builds the bracketed column list with STRING_AGG(QUOTENAME(col), ',') and runs it with sp_executesql.

Avoiding dynamic columns altogether

Often the cleaner answer is to not produce dynamic columns in SQL:

• Return the data long and let the BI tool or pandas pivot it (df.pivot_table(...)).
• Return one row per region with a map or JSON object of month to value:

SELECT region, jsonb_object_agg(sales_month, revenue ORDER BY sales_month) AS revenue_by_month
FROM (SELECT region, sales_month, SUM(revenue) AS revenue FROM monthly_sales GROUP BY 1, 2) t
GROUP BY region
ORDER BY region;

The schema stays fixed however many months arrive.

Pitfalls

• SQL injection: generated SQL that pastes raw values into the statement. Always quote with format('%L')/%I, QUOTENAME or parameters.
• Downstream breakage: tables or views whose columns change every month break dashboards and contracts. Prefer long tables for anything stored.
• Unbounded width: a pivot over a high-cardinality column (customer id) can hit engine column limits.

In interviews

“How would you pivot when you don’t know the values in advance?” The strong answer names the engine feature (DuckDB automatic pivot, Snowflake ANY), explains dynamic SQL with safe quoting for other engines, and then questions the requirement: store long, pivot in the presentation layer, or use a map/JSON column.

GROUPING SETS

GROUPING SETS runs several GROUP BYs in one query and stacks the results. Each set in the list is one grouping; columns not in a set are NULL in that set’s rows.

SELECT region, channel, SUM(revenue) AS revenue
FROM monthly_sales
GROUP BY GROUPING SETS ((region), (channel), ())
ORDER BY region NULLS LAST, channel NULLS LAST;

This is the same as three GROUP BY queries combined with UNION ALL (by region, by channel, and the grand total ()), but the engine scans the data once and the query is shorter.

Telling subtotals from real NULLs: GROUPING()

In those rows, NULL means “all values”, but a real NULL in the data would look the same. GROUPING(col) returns 1 when the column is aggregated away in that row and 0 when it is a real grouping value. Use it to label rows:

SELECT CASE WHEN GROUPING(region)  = 1 THEN 'All regions'  ELSE region  END AS region,
       CASE WHEN GROUPING(channel) = 1 THEN 'All channels' ELSE channel END AS channel,
       SUM(revenue) AS revenue,
       GROUPING(region, channel) AS grouping_id
FROM monthly_sales
GROUP BY GROUPING SETS ((region, channel), (region), ())
ORDER BY GROUPING(region), region, GROUPING(channel), channel;

With several arguments, GROUPING(a, b) returns a bit mask (here 0 = detail, 1 = region subtotal, 3 = grand total), handy for filtering or sorting levels. SQL Server and others also offer GROUPING_ID(...) for the same purpose.

Pitfalls

• Treating subtotal NULLs as missing data, or real NULLs as subtotals. Use GROUPING().
• Applying COALESCE(region, 'All') to label subtotals, which mislabels genuine NULL regions.
• Summing the whole output: it contains detail rows and subtotals, so the sum double counts. Filter to one level first.

In interviews

GROUPING SETS comes up as “produce totals by region, by channel and overall in one query”. Explain the UNION ALL equivalence, the single scan, and GROUPING() for distinguishing subtotal rows.

ROLLUP and CUBE

ROLLUP and CUBE are shorthands for common grouping-set lists.

ROLLUP (a, b, c) produces n + 1 levels; CUBE over n columns produces 2^n groupings, which grows quickly.

ROLLUP: subtotals along a hierarchy

SELECT region, sales_month, SUM(revenue) AS revenue
FROM monthly_sales
GROUP BY ROLLUP (region, sales_month)
ORDER BY region NULLS LAST, sales_month NULLS LAST;

Order matters: ROLLUP (region, sales_month) gives region subtotals, while ROLLUP (sales_month, region) gives month subtotals. There is never a “month across all regions” row here; that would need CUBE or an explicit grouping set.

CUBE: every combination

SELECT region, channel, SUM(revenue) AS revenue
FROM monthly_sales
WHERE sales_month = '2025-03'
GROUP BY CUBE (region, channel)
ORDER BY GROUPING(region), region, GROUPING(channel), channel;

Engine support

MySQL documents CUBE and GROUPING SETS only for its HeatWave engine; in standard MySQL emulate them with UNION ALL of separate GROUP BY queries. Check BigQuery’s current documentation before relying on these clauses there.

Pitfalls

• Choosing the wrong column order in ROLLUP.
• CUBE over many columns producing far more rows (2^n groupings) than anyone reads.
• Mixing ROLLUP with ORDER BY that interleaves subtotal rows unpredictably; sort by GROUPING() first, as above.
• Materialising all levels in one fact table and then double counting when a dashboard sums across them.

In interviews

Expect to explain the difference: ROLLUP follows a hierarchy (n + 1 levels), CUBE gives every combination (2^n), and GROUPING SETS lets you list exactly the levels you want. Mention GROUPING(), the MySQL limitation and the UNION ALL fallback.

Practice questions

SELECT customer_id,
       COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered_count,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
       SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

Key takeaways

• Conditional aggregation (SUM(CASE ...) or FILTER) is the portable pivot; PIVOT operators in DuckDB, Snowflake, SQL Server and others are shorthands with an implicit-grouping trap.
• Unpivot with LATERAL (VALUES ...) or UNION ALL anywhere, or the UNPIVOT operator, which drops NULLs unless told otherwise.
• Dynamic pivots need engine support or generated SQL with safe quoting; storing data long avoids the problem.
• GROUPING SETS computes several groupings in one scan; ROLLUP follows a hierarchy and CUBE covers every combination.
• Use GROUPING() to label subtotal rows, and never sum across levels.
• MySQL supports only ROLLUP; emulate the rest with UNION ALL.