Querying

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.

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

1234567891011

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

	product	year	Q1	Q2
1	apple	2025	77	null
2	apple	2026	1	null
3	kale	2025	51	23
4	kale	2026	70	null

4 rows

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

1234567891011

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

	product	Q1	Q2
1	apple	78	null
2	kale	121	23

2 rows

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 values give NULL, extra values are dropped

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

1234567891011

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

	product	Q1	Q3
1	apple	78	null
2	kale	121	null

2 rows

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

1234567891011

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

	product	total_Q2	n_Q2
1	apple	null	0
2	kale	23	1

2 rows

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

1234567891011

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

	product	total_Q1	orders_Q1	total_Q2	orders_Q2
1	apple	78	2	null	0
2	kale	121	2	23	1

2 rows

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

12345678

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

	sensor	_1	minus_2	three
1	a	5	6	7

1 row

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

Q1 and q1

1234

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

	p	Q1	q1_1
1	a	5	null

1 row

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

1234567891011

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

	product	Q1	Q2
1	apple	1,77	null
2	kale	51,70	23

2 rows

ARRAY_AGG is refused unless you write IGNORE NULLS:

ARRAY_AGG without IGNORE NULLSBigQuery rejects this query

12345678910

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'))

Error in BigQuery

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

1234567891011

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

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

2 rows

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 columnBigQuery rejects this query

12345678910

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))

Error in BigQuery

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 ...)BigQuery rejects this query

12345678910

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))

Error in BigQuery

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:

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.

The same thing 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

123456789101112131415

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

	product	q1	q2	first_amount
1	apple	78	0	77
2	kale	121	23	51

2 rows

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

12345678

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

	product	sales	quarter
1	apple	77	q1
2	apple	0	q2
3	apple	25	q3
4	kale	51	q1
5	kale	23	q2

5 rows

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

12345678

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

	product	sales	quarter
1	apple	77	q1
2	apple	0	q2
3	apple	25	q3
4	kale	51	q1
5	kale	23	q2
6	kale	null	q3

6 rows

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

12345678

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

	product	sales	quarter
1	apple	77	1
2	apple	0	2
3	apple	25	3
4	kale	51	1
5	kale	23	2

5 rows

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 UNPIVOTBigQuery rejects this query

1234

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

Error in BigQuery

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

12345678

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

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

4 rows

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

1234567891011121314

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

	product	amount	quarter
1	apple	78	Q1
2	kale	121	Q1
3	kale	23	Q2

3 rows

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.)