PIVOT keyword

PIVOT transforms rows into columns, converting narrow-schema data into wide-schema format. This is useful for analytics, charting, and transforming time-series sensor data.

Syntax

( selectQuery | tableName )
[ WHERE condition ]
PIVOT (
    aggregateExpression [ AS alias ] [, aggregateExpression [ AS alias ] ...]
    FOR pivotExpression IN ( valueList | selectDistinctQuery )
    [ pivotExpression IN ( valueList | selectDistinctQuery ) ... ]
    [ GROUP BY column [, column ...] ]
) [ AS alias ]
[ ORDER BY column [, column ...] ]
[ LIMIT n ]

Where valueList is: constant [ AS alias ] [, constant [ AS alias ] ...]

Components

Source data

A PIVOT query begins with a result set:

-- From a table name
trades PIVOT ( ... )

-- From a SELECT
SELECT * FROM trades PIVOT ( ... )

-- From a subquery
(SELECT * FROM trades WHERE timestamp > '2024-01-01') PIVOT ( ... )

Aggregate functions

Define one or more aggregations, separated by commas:

PIVOT (
    avg(price),                          -- multiple aggregates
    sum(price * amount) / 2 AS half_value  -- expressions supported
    FOR ...
)

FOR ... IN clause

Specifies which column values become output columns:

-- Static value list
FOR symbol IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT')

-- With aliases for column names
FOR symbol IN ('BTC-USDT' AS bitcoin, 'ETH-USDT' AS ethereum)

-- Dynamic from subquery (executed at parse time)
FOR symbol IN (SELECT DISTINCT symbol FROM trades WHERE timestamp IN '$today')

-- Multiple FOR clauses create Cartesian product
FOR symbol IN ('BTC-USDT', 'ETH-USDT')
    side IN ('buy', 'sell')
-- Produces: BTC-USDT_buy, BTC-USDT_sell, ETH-USDT_buy, ETH-USDT_sell

GROUP BY (optional, inside PIVOT)

Groups results by additional columns:

PIVOT (
    sum(price * amount) / 2
    FOR symbol IN ('BTC-USDT', 'ETH-USDT')
    GROUP BY side    -- inside PIVOT parentheses
)

note

Positional GROUP BY (e.g., GROUP BY 1, 2) is not supported inside PIVOT. Use explicit column names instead.

ORDER BY / LIMIT (outside PIVOT)

Sort and limit the final result set:

trades PIVOT (
    avg(price)
    FOR symbol IN ('BTC-USDT', 'ETH-USDT')
    GROUP BY side
)
ORDER BY side      -- outside PIVOT parentheses
LIMIT 10

Examples

Basic pivot

Transform rows to columns:

Row-based queryDemo this query

SELECT symbol, avg(price)
FROM trades
WHERE timestamp IN '$today'
GROUP BY symbol;

symbol	avg
BTC-USDT	81690.81
ETH-USDT	2388.09
SOL-USDT	88.10
ADA-USDT	0.27
AVAX-USDT	9.60
LTC-USDT	57.17
DOT-USDT	1.31
UNI-USDT	3.45
XLM-USDT	0.16
ETH-BTC	0.03
SOL-BTC	0.001

Without PIVOT, converting rows to columns requires verbose CASE expressions:

Manual pivot with CASEDemo this query

SELECT
    avg(CASE WHEN symbol = 'BTC-USDT' THEN price END) AS "BTC-USDT",
    avg(CASE WHEN symbol = 'ETH-USDT' THEN price END) AS "ETH-USDT",
    avg(CASE WHEN symbol = 'SOL-USDT' THEN price END) AS "SOL-USDT",
    avg(CASE WHEN symbol = 'ADA-USDT' THEN price END) AS "ADA-USDT",
    avg(CASE WHEN symbol = 'AVAX-USDT' THEN price END) AS "AVAX-USDT"
FROM trades
WHERE timestamp IN '$today';

BTC-USDT	ETH-USDT	SOL-USDT	ADA-USDT	AVAX-USDT
81690.82	2388.09	88.10	0.27	9.60

PIVOT simplifies this pattern:

Pivoted to columnsDemo this query

SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price)
    FOR symbol IN (
        'BTC-USDT', 'ETH-USDT', 'SOL-USDT',
        'ADA-USDT', 'AVAX-USDT'
    )
);

BTC-USDT	ETH-USDT	SOL-USDT	ADA-USDT	AVAX-USDT
81683.77	2387.93	88.09	0.27	9.60

Multiple aggregates

Multiple aggregates per symbolDemo this query

SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price) AS avg_price,
    sum(price * amount) / 2 AS half_value
    FOR symbol IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT')
);

BTC-USDT_avg_price	BTC-USDT_half_value	ETH-USDT_avg_price	ETH-USDT_half_value	SOL-USDT_avg_price	SOL-USDT_half_value
81683.99	154998570.76	2387.94	80641109.01	88.09	24492519.72

Multiple FOR clauses (Cartesian product)

Pivot by symbol and sideDemo this query

SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price)
    FOR symbol IN (
        'BTC-USDT', 'ETH-USDT', 'SOL-USDT',
        'ADA-USDT', 'AVAX-USDT'
    )
        side IN ('buy', 'sell')
);

BTC-USDT_buy	BTC-USDT_sell	ETH-USDT_buy	ETH-USDT_sell	SOL-USDT_buy	SOL-USDT_sell	ADA-USDT_buy	ADA-USDT_sell	AVAX-USDT_buy	AVAX-USDT_sell
81717.80	81645.93	2387.97	2387.92	88.13	88.05	0.2668	0.2671	9.6085	9.5972

With GROUP BY

Keep additional dimensions as rows:

Pivot with GROUP BYDemo this query

SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price)
    FOR symbol IN (
        'BTC-USDT', 'ETH-USDT', 'SOL-USDT',
        'ADA-USDT', 'AVAX-USDT'
    )
    GROUP BY side
) ORDER BY side;

side	BTC-USDT	ETH-USDT	SOL-USDT	ADA-USDT	AVAX-USDT
buy	81717.85	2387.97	88.13	0.2668	9.6085
sell	81645.99	2387.92	88.05	0.2671	9.5972

note

When a GROUP BY key has no matching FOR values in the data, the entire row is excluded from results rather than appearing with NULL pivot columns. This is due to filter optimization that pushes FOR column IN (values) to the WHERE clause.

For example, if side = 'hold' exists but has no matching symbols, that row won't appear.

Dynamic IN list from subquery

Column names determined at query compile time:

Dynamic pivot columns from subqueryDemo this query

SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price)
    FOR symbol IN (
        SELECT DISTINCT symbol FROM trades
        WHERE timestamp IN '$today'
        ORDER BY symbol
    )
    GROUP BY side
);

warning

Subqueries in the IN clause are executed at parse time, not at runtime. Changes to the source table after query compilation won't affect column names.

note

Subqueries in the IN clause must:

• Return exactly one column
• Return at least one row (empty result sets cause an error)

tip

If the subquery runs on a large table, it can slow down the overall PIVOT query. For exploratory analysis, dynamic subqueries are convenient. For production queries, use a constant list or store keys in a small dimension table for better performance.

With CTEs

Pivot with CTEDemo this query

WITH recent_trades AS (
    SELECT * FROM trades
    WHERE timestamp IN '$today'
)
SELECT * FROM recent_trades
PIVOT (
    avg(price)
    FOR symbol IN (
        SELECT DISTINCT symbol FROM recent_trades
        ORDER BY symbol
    )
    GROUP BY side
);

Column naming

Output columns are automatically named based on the combination of FOR values and aggregates.

With a single aggregate, columns are named using just the FOR value:

Single aggregate column namesDemo this query

-- Columns: BTC-USDT, ETH-USDT
SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price)
    FOR symbol IN ('BTC-USDT', 'ETH-USDT')
);

With multiple aggregates, the full function expression is included:

Multiple aggregate column namesDemo this query

-- Columns: BTC-USDT_avg(price), BTC-USDT_sum(price), ...
SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price), sum(price)
    FOR symbol IN ('BTC-USDT', 'ETH-USDT')
);

Use aliases for cleaner column names:

Aliased column namesDemo this query

-- Columns: BTC-USDT_avg_price, BTC-USDT_total_price, ...
SELECT * FROM trades
WHERE timestamp IN '$today'
PIVOT (
    avg(price) AS avg_price, sum(price) AS total_price
    FOR symbol IN ('BTC-USDT', 'ETH-USDT')
);

Scenario	Example	Column name
Single aggregate	avg(price) FOR symbol IN ('BTC-USDT')	BTC-USDT
Multiple aggregates	avg(price), sum(price) FOR symbol IN ('BTC-USDT')	BTC-USDT_avg(price), BTC-USDT_sum(price)
Multiple FOR	avg(price) FOR symbol IN ('BTC-USDT') side IN ('buy')	BTC-USDT_buy
With alias on value	FOR symbol IN ('BTC-USDT' AS btc)	btc
With alias on aggregate	avg(price) AS avg_price FOR symbol IN ('BTC-USDT')	BTC-USDT_avg_price

Limits

PIVOT has a configurable maximum column limit (default: 5000) to prevent excessive memory usage. The total columns = (FOR value combinations) × (number of aggregates).

-- Fails if combinations × aggregates exceeds 5000
trades PIVOT (
    avg(price)
    FOR symbol IN (SELECT DISTINCT symbol FROM trades)  -- many symbols
        side IN ('buy', 'sell')                         -- x 2
);

See also

• GROUP BY - Row-based aggregation
• SAMPLE BY - Time-series aggregation
• Aggregation functions - Functions available in PIVOT
• WITH - Using PIVOT with common table expressions