# PIVOT and UNPIVOT in BigQuery

> BigQuery has PIVOT and UNPIVOT operators in the FROM clause. PIVOT groups by every column you don't mention, and its value list must be constants, so a dynamic pivot needs EXECUTE IMMEDIATE. UNPIVOT drops NULLs by default.

Source: https://docs.querylab.io/bigquery/pivot-and-unpivot/

Updated: 2026-10-06

`PIVOT` turns the distinct values of one column into columns, using an aggregate to fill them. `UNPIVOT` does the reverse and turns columns into rows. Both are operators on a table in the `FROM` clause, written after the table or subquery, and both need the list of values or columns spelled out in the query.

## PIVOT groups by everything you don't use

The syntax is `PIVOT(aggregate FOR column IN (value, ...))`. There is no `GROUP BY`: BigQuery groups by every column of the input that is neither the aggregated column nor the `FOR` column. In this example `year` is left over, so it becomes part of the grouping:

**one row per product and year**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM sales
PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2'))
ORDER BY product, year
```

Result in BigQuery (4 rows):

| product | year | Q1 | Q2 |
| --- | --- | --- | --- |
| apple | 2025 | 77 | NULL |
| apple | 2026 | 1 | NULL |
| kale | 2025 | 51 | 23 |
| kale | 2026 | 70 | NULL |

Kale has two rows, one for 2025 and one for 2026, not one row per product. If you want one row per product, the fix is to select only the columns you need in a subquery:

**drop year before pivoting**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2'))
ORDER BY product
```

Result in BigQuery (2 rows):

| product | Q1 | Q2 |
| --- | --- | --- |
| apple | 78 | NULL |
| kale | 121 | 23 |

Now the 2025 and 2026 sales add up: apple Q1 is 77 + 1 = 78. So pivot from a subquery that selects only the row key, the `FOR` column and the value.

## Missing and extra pivot values

A value in the `IN` list that doesn't occur in the data gives a column of NULLs. A value in the data that is not in the list is dropped without a warning. Here `Q2` has data but isn't listed, and `Q3` is listed but has no data:

**Q2 dropped, Q3 all NULL**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q3'))
ORDER BY product
```

Result in BigQuery (2 rows):

| product | Q1 | Q3 |
| --- | --- | --- |
| apple | 78 | NULL |
| kale | 121 | NULL |

The 23 for kale's Q2 is gone from the result. If new values show up in the data later, a fixed `IN` list will ignore them.

## SUM gives NULL, COUNT gives 0

When no rows match a cell, the aggregate runs over zero rows. `SUM` returns NULL for that, and `COUNT` returns 0:

**apple has no Q2 rows**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(SUM(amount) AS total, COUNT(amount) AS n FOR quarter IN ('Q2'))
ORDER BY product
```

Result in BigQuery (2 rows):

| product | total_Q2 | n_Q2 |
| --- | --- | --- |
| apple | NULL | 0 |
| kale | 23 | 1 |

Wrap `SUM` in `IFNULL(..., 0)` outside the `PIVOT` if you want zeros.

## Several aggregates and column names

With more than one aggregate, give each an alias. BigQuery builds the column names as alias, underscore, value:

**total_Q1, orders_Q1, total_Q2, orders_Q2**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(SUM(amount) AS total, COUNT(*) AS orders FOR quarter IN ('Q1', 'Q2'))
ORDER BY product
```

Result in BigQuery (2 rows):

| product | total_Q1 | orders_Q1 | total_Q2 | orders_Q2 |
| --- | --- | --- | --- | --- |
| apple | 78 | 2 | NULL | 0 |
| kale | 121 | 2 | 23 | 1 |

Without a value alias, the column name comes from the value. A string value is used as is, a positive number gets a leading underscore (`_1`), a negative one is prefixed with `minus_`, and a date becomes `_2026_01_02`. Add `AS name` to choose your own:

**numeric values as column names**

```sql
WITH readings AS (
  SELECT 'a' AS sensor, 1 AS level, 5 AS v UNION ALL
  SELECT 'a', -2, 6 UNION ALL
  SELECT 'a', 3, 7
)
SELECT *
FROM readings
PIVOT(SUM(v) FOR level IN (1, -2, 3 AS three))
```

Result in BigQuery (1 row):

| sensor | _1 | minus_2 | three |
| --- | --- | --- | --- |
| a | 5 | 6 | 7 |

Two values that map to the same name get a numeric suffix. Names are case-insensitive, so `'Q1'` and `'q1'` clash:

**Q1 and q1**

```sql
WITH t AS (SELECT 'a' AS p, 'Q1' AS q, 5 AS v)
SELECT *
FROM t
PIVOT(SUM(v) FOR q IN ('Q1', 'q1'))
```

Result in BigQuery (1 row):

| p | Q1 | q1_1 |
| --- | --- | --- |
| a | 5 | NULL |

## What the aggregate may be

Google's page says the aggregate must take one argument and ignore NULLs. In practice `STRING_AGG` with a separator works, including its own `ORDER BY`:

**STRING_AGG with a separator**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(STRING_AGG(CAST(amount AS STRING), ',' ORDER BY amount) FOR quarter IN ('Q1', 'Q2'))
ORDER BY product
```

Result in BigQuery (2 rows):

| product | Q1 | Q2 |
| --- | --- | --- |
| apple | 1,77 | NULL |
| kale | 51,70 | 23 |

`ARRAY_AGG` is refused unless you write `IGNORE NULLS`:

**ARRAY_AGG without IGNORE NULLS**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(ARRAY_AGG(amount) FOR quarter IN ('Q1', 'Q2'))
```

BigQuery rejects this query:

```text
Use of aggregate function ARRAY_AGG as PIVOT expression is not supported unless IGNORE NULLS is specified at [10:7]
```

With `IGNORE NULLS` it works, and a cell with no rows is an empty array, not NULL:

**ARRAY_AGG with IGNORE NULLS**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(ARRAY_AGG(amount IGNORE NULLS ORDER BY amount) FOR quarter IN ('Q1', 'Q2'))
ORDER BY product
```

Result in BigQuery (2 rows):

| product | Q1 | Q2 |
| --- | --- | --- |
| apple | [1, 77] | [] |
| kale | [51, 70] | [23] |

The values in the `IN` list must have the same type as the `FOR` column. Pivoting a string column on `1, 2` fails:

**numbers against a STRING column**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(SUM(amount) FOR quarter IN (1, 2))
```

BigQuery rejects this query:

```text
PIVOT IN list item must be type STRING to match the PIVOT FOR expression; found INT64 at [10:35]
```

## No dynamic column list

The `IN` list must be constants. A subquery is a syntax error. A query parameter works only with an explicit alias: `IN (@q AS first_q)` passes, while `IN (@q)` is rejected with "Generating an implicit alias for this PIVOT value is not supported". Each parameter is still one fixed column, so parameters don't make the column list dynamic:

**IN (SELECT DISTINCT ...)**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (SELECT product, quarter, amount FROM sales)
PIVOT(SUM(amount) FOR quarter IN (SELECT DISTINCT quarter FROM sales))
```

BigQuery rejects this query:

```text
Syntax error: Unexpected keyword SELECT at [10:35]
```

To pivot on whatever values the data has, build the query as a string and run it with `EXECUTE IMMEDIATE`. We ran this script on BigQuery. It is not run on the page:

```sql
DECLARE cols STRING;
CREATE TEMP TABLE sales AS
SELECT 'kale' AS product, 'Q1' AS quarter, 5 AS amount UNION ALL
SELECT 'kale', 'Q2', 7 UNION ALL
SELECT 'apple', 'Q3', 1;

SET cols = (
  SELECT STRING_AGG(FORMAT('%T', q), ', ' ORDER BY q)
  FROM (SELECT DISTINCT quarter AS q FROM sales)
);

EXECUTE IMMEDIATE FORMAT(
  'SELECT * FROM sales PIVOT(SUM(amount) FOR quarter IN (%s)) ORDER BY product', cols);
-- product | Q1   | Q2   | Q3
-- apple   | NULL | NULL | 1
-- kale    | 5    | 7    | NULL
```

`FORMAT('%T', ...)` quotes each value as a SQL literal.

## Pivoting with conditional aggregation

`PIVOT` is shorthand for a `GROUP BY` with one conditional aggregate per value. Writing it out is longer, but each column can use a different aggregate or condition:

**SUM(IF(...)) per quarter**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT
  product,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2,
  ARRAY_AGG(amount ORDER BY year LIMIT 1)[OFFSET(0)] AS first_amount
FROM sales
GROUP BY product
ORDER BY product
```

Result in BigQuery (2 rows):

| product | q1 | q2 | first_amount |
| --- | --- | --- | --- |
| apple | 78 | 0 | 77 |
| kale | 121 | 23 | 51 |

## UNPIVOT: columns to rows

`UNPIVOT(values FOR names IN (col, ...))` creates two new columns: one with the values from the listed columns, one with the column names. Columns you don't list stay as they are:

**q1, q2, q3 become rows**

```sql
WITH quarterly AS (
  SELECT 'kale' AS product, 51 AS q1, 23 AS q2, NULL AS q3 UNION ALL
  SELECT 'apple', 77, 0, 25
)
SELECT *
FROM quarterly
UNPIVOT(sales FOR quarter IN (q1, q2, q3))
ORDER BY product, quarter
```

Result in BigQuery (5 rows):

| product | sales | quarter |
| --- | --- | --- |
| apple | 77 | q1 |
| apple | 0 | q2 |
| apple | 25 | q3 |
| kale | 51 | q1 |
| kale | 23 | q2 |

Kale's `q3` is NULL and has no row in the result: five rows, not six. `UNPIVOT` is `EXCLUDE NULLS` by default. Write `INCLUDE NULLS` to keep them:

**UNPIVOT INCLUDE NULLS**

```sql
WITH quarterly AS (
  SELECT 'kale' AS product, 51 AS q1, 23 AS q2, NULL AS q3 UNION ALL
  SELECT 'apple', 77, 0, 25
)
SELECT *
FROM quarterly
UNPIVOT INCLUDE NULLS (sales FOR quarter IN (q1, q2, q3))
ORDER BY product, quarter
```

Result in BigQuery (6 rows):

| product | sales | quarter |
| --- | --- | --- |
| apple | 77 | q1 |
| apple | 0 | q2 |
| apple | 25 | q3 |
| kale | 51 | q1 |
| kale | 23 | q2 |
| kale | NULL | q3 |

The name column holds the column names as written, `q1`, `q2`. To use other labels, give each column an alias. The aliases must all be strings or all be `INT64` literals, and with `INT64` every column needs one (mixing them is an error: "All UNPIVOT labels must be the same type"):

**numeric labels**

```sql
WITH quarterly AS (
  SELECT 'kale' AS product, 51 AS q1, 23 AS q2, NULL AS q3 UNION ALL
  SELECT 'apple', 77, 0, 25
)
SELECT *
FROM quarterly
UNPIVOT(sales FOR quarter IN (q1 AS 1, q2 AS 2, q3 AS 3))
ORDER BY product, quarter
```

Result in BigQuery (5 rows):

| product | sales | quarter |
| --- | --- | --- |
| apple | 77 | 1 |
| apple | 0 | 2 |
| apple | 25 | 3 |
| kale | 51 | 1 |
| kale | 23 | 2 |

Every unpivoted column must have the same type, and BigQuery does not coerce, even where a common supertype exists. A STRING next to an INT64 fails:

**INT64 and STRING in one UNPIVOT**

```sql
WITH t AS (SELECT 'kale' AS product, 51 AS q1, 'n/a' AS q2)
SELECT *
FROM t
UNPIVOT(sales FOR quarter IN (q1, q2))
```

BigQuery rejects this query:

```text
The datatype of column does not match other datatypes in the IN clause. Expected INT64, Found STRING at [4:35]
```

Cast first in a subquery if the types differ.

## Unpivoting several columns together

To unpivot pairs of columns at once, put a list of value columns in parentheses and list the column sets in `IN`. Each set becomes one row, labelled by its alias:

**two value columns, one label**

```sql
WITH teams AS (
  SELECT 1 AS team_id, 'ana' AS lead, 'ben' AS deputy, 'cy' AS intern, 'di' AS mentor UNION ALL
  SELECT 2, 'ed', 'flo', 'gus', 'hal'
)
SELECT *
FROM teams
UNPIVOT((senior, junior) FOR pair IN ((lead, deputy) AS 'first', (intern, mentor) AS 'second'))
ORDER BY team_id, pair
```

Result in BigQuery (4 rows):

| team_id | senior | junior | pair |
| --- | --- | --- | --- |
| 1 | ana | ben | first |
| 1 | cy | di | second |
| 2 | ed | flo | first |
| 2 | gus | hal | second |

## PIVOT and UNPIVOT are not inverses

`UNPIVOT` can't undo a `PIVOT`, because the pivot already summed rows together and turned missing cells into NULLs, which `UNPIVOT` then drops. Pivoting and unpivoting the sales from the start of this article returns one row per product and quarter with the totals, and apple's empty Q2 is gone:

**round trip loses the detail rows**

```sql
WITH sales AS (
  SELECT 'kale' AS product, 'Q1' AS quarter, 2025 AS year, 51 AS amount UNION ALL
  SELECT 'kale', 'Q2', 2025, 23 UNION ALL
  SELECT 'kale', 'Q1', 2026, 70 UNION ALL
  SELECT 'apple', 'Q1', 2025, 77 UNION ALL
  SELECT 'apple', 'Q1', 2026, 1
)
SELECT *
FROM (
  SELECT * FROM (SELECT product, quarter, amount FROM sales)
  PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2'))
)
UNPIVOT(amount FOR quarter IN (Q1, Q2))
ORDER BY product, quarter
```

Result in BigQuery (3 rows):

| product | amount | quarter |
| --- | --- | --- |
| apple | 78 | Q1 |
| kale | 121 | Q1 |
| kale | 23 | Q2 |

The columns in `UNPIVOT`'s `IN` list must be plain column names. An expression such as `a + 1` is a syntax error. (Google's page also rules out qualified names like `t.a`, but BigQuery accepts them.)

Pivot a table and see which columns you get. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, PIVOT operator](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator)

- [Query syntax, UNPIVOT operator](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unpivot_operator)