PIVOT and UNPIVOT

PIVOT and UNPIVOT#

PIVOT and UNPIVOT are postfix table operators in the FROM clause. PIVOT turns the values of one column into columns of its own, aggregating the rows that fall into each of them. UNPIVOT does the opposite: it turns a list of columns into rows, one row per column and source row.

Both operators require a final alias, and that alias replaces the alias of their input for the rest of the query.

Syntax#

<table_source> PIVOT (
        <aggregate_function> ( <value_column> )
        FOR <pivot_column> IN ( [ <first_bucket> ] [ ,...n ] )
    ) [ AS ] <table_alias>

<table_source> UNPIVOT (
        <value_column> FOR <name_column> IN ( <source_column> [ ,...n ] )
    ) [ AS ] <table_alias>

Arguments#

table_source

The input of the rotation: a table, a view, a derived table, or a whole join chain. A rotation may also be applied to the result of another rotation.

aggregate_function

One of SUM, COUNT, COUNT_BIG, AVG, MIN and MAX, in any letter case. The argument is a single column reference; *, an expression, DISTINCT, ALL and an OVER clause are rejected by the parser. Any other aggregate name is rejected with error 195, and CHECKSUM_AGG with error 406, because it is not invariant to NULLs.

pivot_column

The column whose values become output columns. Every value listed in IN is converted to this column’s type; a value that cannot be converted is rejected with error 8114.

first_bucket

An identifier - usually delimited, as in [2025]. Each entry names one output column and, converted to the pivot column’s type, selects the rows aggregated into it. Entries may not be qualified with a table alias (error 254), may not repeat under the catalog collation (error 8156), and may not collide with a column the rotation keeps (error 265).

value_column and name_column (UNPIVOT)

The two columns the rotation generates: value_column carries the cell value and name_column the name of the column it came from. Neither identifier may be qualified (error 275), and the two must differ (error 8156).

source_column

A column of the input to turn into rows. A column may be listed only once (error 277). Every listed column must have exactly the same type - including length, scale and collation (error 8167); there is no common-supertype coercion.

table_alias

Required. Once the rotation is applied, the input’s alias is no longer visible: a query that still references it fails with error 4104.

Remarks#

What PIVOT returns. Every input column that is neither the aggregate argument nor the pivot column becomes an implicit grouping column, and the result keeps those columns in source order followed by the IN entries in list order. Generated columns are always nullable, including COUNT and COUNT_BIG buckets, and take the type the aggregate returns. A bucket that matches no row aggregates to NULL - except for the counting aggregates, which return 0.

What UNPIVOT returns. The result keeps the columns that were not listed, then the value column, then the name column. Rows whose cell is NULL are not emitted. The value column takes the exact common type of the listed columns and is nullable only when at least one of them is; the name column is nvarchar(128) with the collation of the virtual catalog and carries each source column’s declared spelling.

Where the rotation runs. Querona executes the operator on the data source whenever the source can run it, and otherwise rewrites it into equivalent standard SQL:

Source	PIVOT	UNPIVOT
SQL Server 2012 and later	Executed by the source	Executed by the source
Azure Synapse Analytics	Federation engine	Federation engine
PostgreSQL 16 and later	Rewritten	Rewritten
MySQL and MariaDB	Rewritten	Rewritten
Vertica	Rewritten	Rewritten
StarRocks 4	Executed by the source, rewritten when the rotation keeps no grouping column	Rewritten
DuckDB	Executed by the source, rewritten when the rotation keeps no grouping column	Executed by the source
Any other source	Federation engine	Federation engine

A rewritten PIVOT becomes conditional aggregation - one SUM(CASE WHEN ... THEN ... END) per bucket over a GROUP BY of the grouping columns. A rewritten UNPIVOT becomes a UNION ALL of one filtered branch per listed column. Both rewrites return exactly what the native operator returns.

When neither the source nor the rewrite applies, the rotation runs on the federation engine and only its input is read from the source. If no federation engine can run it either, the query fails with a message naming the operator and the executor that refused it, rather than returning a different result.

Not currently supported

Azure Synapse Analytics executes neither operator, and neither does PostgreSQL before version 16; queries against them fall back to the federation engine.
A rotation whose pivot column or aggregate argument declares a collation different from the virtual catalog’s is refused instead of executed under a guessed collation.
A rotation is not rewritten when its input is not safe to read more than once - for example when the input calls GETDATE() - or when an UNPIVOT would need more branches than the configured maximum. In both cases the rotation runs on the federation engine.
A rotation that the data source executes itself cannot be combined with data from another source in the same statement - for example a PIVOT over a SQL Server table joined with a table from a different connection. Such a query is refused with an error naming the operator and its source. Read the rotated data from a single source, or materialize the rotation result first.
When a statement produces one of SQL Server’s ordered error pairs, Querona reports the first error of the pair.

Permissions#

Requires SELECT on the objects the rotation reads. The operator itself needs no additional permission.

Examples#

A. Turning years into columns#

SELECT CustomerId, [2025], [2026]
FROM (SELECT CustomerId, FiscalYear, Amount FROM Sales) AS s
PIVOT (SUM(s.Amount) FOR s.FiscalYear IN ([2025], [2026])) AS p
ORDER BY CustomerId ;

CustomerId is the only column left after the aggregate argument and the pivot column, so it becomes the grouping column. A customer with no rows in a year gets NULL in that year’s column.

B. Turning columns into rows#

SELECT CustomerId, MetricName, MetricValue
FROM Metrics AS m
UNPIVOT (MetricValue FOR MetricName IN (Revenue, Cost, Margin)) AS u
ORDER BY CustomerId, MetricName ;

Each source row yields up to three rows. A metric that is NULL for a customer produces no row at all, so use a LEFT JOIN against the list of metric names if the gaps matter.

C. Counting instead of summing#

SELECT CustomerId, [2025], [2026]
FROM (SELECT CustomerId, FiscalYear, Amount FROM Sales) AS s
PIVOT (COUNT(s.Amount) FOR s.FiscalYear IN ([2025], [2026])) AS p ;

Counting buckets return 0 where there is nothing to count, while SUM, AVG, MIN and MAX return NULL.

D. Rotating the result of a rotation#

SELECT *
FROM Sales AS s
PIVOT (SUM(s.Amount) FOR s.FiscalYear IN ([2025], [2026])) AS p
UNPIVOT (Amount FOR FiscalYear IN ([2025], [2026])) AS u ;

The second rotation reads the output of the first one, so it may reference the generated columns [2025] and [2026] but no longer the alias s.