ClickHouse PIVOT and UNPIVOT: Syntax, Workarounds, and Column-to-Row Transformations

ClickHouse does not have a PIVOT or UNPIVOT clause. Unlike SQL Server, Oracle, or Snowflake, there is no dedicated keyword for transposing rows into columns or columns into rows. Queries that try one usually fail to parse — often with an `Unknown element in AST` error.

The good news: every pivot and unpivot pattern can be reproduced with native ClickHouse features — conditional aggregation (sumIf, countIf), map-based aggregate combinators (sumMap), and the `ARRAY JOIN` clause. This guide covers each approach, when to use it, and the trade-offs.

PIVOT: Rows to Columns

Pivoting turns distinct values from one column into separate result columns. The right technique depends on whether you know the target columns in advance.

Conditional Aggregation (Known Columns)

When the pivot values are known ahead of time, conditional aggregation is the clearest and fastest method. Append the `-If` combinator to any aggregate function so it only processes rows matching a condition:

SELECT
    suppkey,
    sumIf(quantity, category = 'AA') AS AA,
    sumIf(quantity, category = 'AB') AS AB,
    sumIf(quantity, category = 'AC') AS AC,
    sumIf(quantity, category = 'AD') AS AD
FROM sales
GROUP BY suppkey
ORDER BY suppkey ASC;

Each sumIf becomes one pivoted column. The whole pivot is computed in a single pass over the data, which is why this is the recommended approach for static reports. The same pattern works with countIf, avgIf, maxIf, uniqIf, and any other -If aggregate.

You can also write the condition with CASE / multiIf inside a plain aggregate, which is equivalent:

SELECT
    suppkey,
    sum(CASE WHEN category = 'AA' THEN quantity ELSE 0 END) AS AA,
    sum(CASE WHEN category = 'AB' THEN quantity ELSE 0 END) AS AB
FROM sales
GROUP BY suppkey;

Map-Based Aggregation (Many or Unknown Columns)

When there are too many categories to list, or the set isn't known until runtime, use the -Map aggregate combinator. sumMap aggregates values keyed by category into a single Map column:

WITH CAST(sumMap([category], [quantity]), 'Map(String, UInt32)') AS m
SELECT
    suppkey,
    m['AA'] AS AA,
    m['AB'] AS AB,
    m['AC'] AS AC,
    m['AD'] AS AD
FROM sales
GROUP BY suppkey
ORDER BY suppkey ASC;

The map version computes the full pivot once; individual columns are then extracted by key. If you don't need fixed columns at all, simply return the map and let the client expand it:

SELECT
    suppkey,
    sumMap([category], [quantity]) AS quantities
FROM sales
GROUP BY suppkey;

sumMap is part of a family — minMap, maxMap, avgMap, and medianMap work the same way. For example, pivoting prices by year per county:

WITH year(toStartOfInterval(date, toIntervalYear(10))) AS year
SELECT
    county,
    medianMap(map(year, price)) AS medianPrices,
    maxMap(map(year, price)) AS maxPrices
FROM uk.uk_price_paid
WHERE year >= 2010
GROUP BY ALL;

Truly Dynamic Pivots

ClickHouse query results have a fixed column list determined at parse time, so a single SQL query cannot return columns whose names depend on the data. You have two realistic options:

1. Return a Map (or array) column and let the application render it into a table — usually the cleanest choice.
2. Generate the SQL dynamically: run a first query to fetch the distinct categories, then build the sumIf(...) AS <category> list in your application and execute the second query. This is how BI tools and reporting layers implement "dynamic pivot" on ClickHouse.

UNPIVOT: Columns to Rows

Unpivoting is the inverse: it takes several columns and stacks their values into rows. In ClickHouse this is the job of the `ARRAY JOIN` clause, which expands an array into one row per element.

ARRAY JOIN with Parallel Arrays

Put the column values into one array and the corresponding column names into a parallel array, then ARRAY JOIN both at once:

SELECT
    suppkey,
    brand,
    category,
    quantity
FROM sales_w
ARRAY JOIN
    [AA, AB, AC, AD] AS quantity,
    ['AA', 'AB', 'AC', 'AD'] AS category
ORDER BY suppkey ASC;

Each source row becomes four rows — one per (category, quantity) pair. The two arrays are iterated in lockstep, so element 1 of quantity pairs with element 1 of category, and so on.

Unpivot via Tuples

If you'd rather not maintain the column-name list by hand, build a named tuple and let tupleToNameValuePairs derive the labels from the tuple field names:

SELECT
    suppkey,
    brand,
    tpl.1 AS category,
    tpl.2 AS quantity
FROM sales_w
ARRAY JOIN tupleToNameValuePairs(
    CAST((AA, AB, AC, AD), 'Tuple(AA UInt32, AB UInt32, AC UInt32, AD UInt32)')
) AS tpl;

This keeps the column names and values together, which reduces the chance of misaligning the two parallel arrays.

Filtering Out NULLs / Zeros

A standard UNPIVOT in other databases drops rows where the value is NULL. Replicate that with a WHERE (or HAVING) after the ARRAY JOIN:

SELECT suppkey, category, quantity
FROM sales_w
ARRAY JOIN
    [AA, AB, AC, AD] AS quantity,
    ['AA', 'AB', 'AC', 'AD'] AS category
WHERE quantity != 0;

Use LEFT ARRAY JOIN instead of ARRAY JOIN if you need to keep source rows whose arrays are empty (they produce a single row with default values rather than disappearing).

Common Issues

1. Unknown element in AST on PIVOT/UNPIVOT: there is no such keyword in ClickHouse. Rewrite using the patterns above. See Unknown element in AST.

2. Dynamic column names don't work in pure SQL: column names are fixed at parse time. Return a Map/array, or generate SQL in two steps.

3. Misaligned parallel arrays in UNPIVOT: the value array and the name array must have the same length and ordering. The tuple + tupleToNameValuePairs approach avoids this class of bug.

4. Multiple arrayJoin calls collapsing: calling arrayJoin more than once in a SELECT can yield surprising results due to common subexpression elimination. Prefer a single ARRAY JOIN clause that expands all arrays together.

5. Empty arrays vanish: ARRAY JOIN drops rows whose array is empty. Use LEFT ARRAY JOIN to preserve them.

Best Practices

1. Prefer sumIf for known, small pivots: it is the most readable, computes everything in one pass, and produces real named columns that any client understands.

2. Use sumMap for wide or unknown category sets: a single Map column scales far better than dozens of hand-written sumIf expressions.

3. Push dynamic pivoting to the application or BI layer: ClickHouse can't name columns from data, so let the client expand a map/array. This is normal and expected.

4. Use ARRAY JOIN for unpivot, not UNION ALL: stacking columns with many UNION ALL branches scans the table once per branch; ARRAY JOIN scans once.

5. Pair value and name arrays via tuples when unpivoting many columns to avoid alignment mistakes.

6. Consider data modeling instead: if you constantly unpivot, storing data in a tall (key/value) layout — or using a `Map` or array column at write time — may remove the need entirely.

How Pulse Helps

Pivot and unpivot rewrites are easy to get subtly wrong: a sumIf pivot that silently drops a category, a parallel-array unpivot with mismatched lengths, or a dynamic-pivot query that scans the table dozens of times. Pulse provides 24/7 monitoring and expert support for ClickHouse, helping teams spot inefficient query patterns, validate query rewrites, and tune schemas so reshaping data stays fast as tables grow. If you're migrating PIVOT/UNPIVOT workloads from another database, Pulse's ClickHouse experts can review the rewrites and the underlying table design.

Frequently Asked Questions

Q: Does ClickHouse support the PIVOT keyword?

No. ClickHouse has no PIVOT or UNPIVOT SQL clause. Use conditional aggregation (sumIf, countIf) or -Map aggregate combinators (sumMap) to pivot, and ARRAY JOIN to unpivot.

Q: How do I pivot when I don't know the column values in advance?

Use sumMap to aggregate into a single Map column and have your application render it, or run a first query to fetch the distinct values and generate the sumIf(...) AS <value> list dynamically before running the pivot.

Q: What is the fastest way to pivot a small, fixed set of categories?

Conditional aggregation with sumIf (or countIf/avgIf). All columns are computed in a single pass over the table and the result has real named columns.

Q: How do I unpivot columns into rows in ClickHouse?

Place the column values in an array and the column names in a parallel array, then expand both with a single ARRAY JOIN. The tupleToNameValuePairs + tuple variant keeps names and values aligned automatically.

Q: Why does my UNPIVOT drop some source rows?

ARRAY JOIN removes rows whose array is empty. Use LEFT ARRAY JOIN to keep them, and add a WHERE value IS NOT NULL (or != 0) only if you intentionally want to filter out missing values.

Q: Can I get a true dynamic-column pivot in one query?

No. A ClickHouse query's column list is fixed when the query is parsed, so columns cannot be named from the data within a single statement. Return a Map/array and expand it client-side, or generate the SQL in two steps.