pivot ↗

Use the pivot stage to rotate row-level data into columns, aligning with BigQuery's native PIVOT operator. The Pivot stage applies one or more aggregation functions to a specified field and transposes the distinct values of a pivot column (specified in the FOR / IN clause) into new output columns. This is useful for transforming long-format data into a wide-format summary, making it easier to compare values side by side.

The pivot stage transforms row-oriented data into a columnar summary by:

1. Selecting a column whose distinct values become new column headers (the pivot key, specified in the IN clause).
2. Applying one or more aggregate functions to compute the values that populate those new columns.
3. Optionally grouping the remaining rows by a by clause to produce one output column per group.

Syntax

pivot <aggregation_function>(<field>)[, <aggregation_function2>(<field2>), ...] for <pivot_column> IN ("<value1>", "<value2>", ...) [by <group_field1>[, <group_field2>, ...]]

Parameters

Name	Type	Required	Description
aggregation_function	function	Yes	The aggregation function to apply to the target field. Allowed functions: count, sum, avg, min, max, count_distinct, approx_count, var, stddev_sample, stddev_population, approx_quantiles, list. A maximum of 3 aggregation functions can be used within a single pivot stage. The allowed aggregation functions are subject to tenant configuration.
field	string	Yes	The name of the field whose values are aggregated.
pivot_column	string	Yes	The field (specified after for) whose distinct values become the new column names in the output. Must resolve to a primitive data type — JSON fields and Records cannot be used as the pivot key (see Limitations).
value1, value2, ...	string	Yes	A comma-separated list of quoted values from the pivot_column that define which new columns to create. Each value becomes a separate column in the result. A maximum of 10 values can be specified in the IN clause.
group_field	string	No	One or more fields specified after by that group the output rows. When the by clause is used, the total resulting group columns are capped to 5 values. If omitted, the output consists only of the aggregated data across the IN values.

Returns

The pivot stage returns a transformed dataset where:

• The pivot_column is removed from the output schema.
• A new column is created for each value specified in the IN (...) clause, named after that value.
• Each new column contains the result of the aggregation function applied to the field for the corresponding pivot_column value.
• When a single aggregation function is used, the new columns are named directly after the IN values (for example, NETWORK, FILE, PROCESS).
• When multiple aggregation functions are used, the new columns follow the naming pattern <function>_<index>_<value> (for example, sum_1_NETWORK, max_2_FILE).
• If the by clause is specified, the group fields are preserved in the output. If omitted, only the aggregated pivot columns are returned.
• All other fields from the input dataset that are not part of the aggregation or pivot key are discarded.

Usage notes

• The pivot stage is a "blocking" operation; it must process all input records before producing any output.
• The values listed in the IN (...) clause must be quoted strings.
• Only one IN condition with a single set of values is supported per pivot stage.
• The pivot stage is not supported in Materialized Views.
• The pivot stage is typically used after a data source stage (such as dataset) and can be combined with other stages like filter, alter, sort, comp, or fields for further processing.
• After a pivot, the newly created columns (from the IN clause values) become regular columns in the dataset. They can be referenced in any subsequent stage — filter, alter, sort, fields, etc. — just like any other column.

Limitations

The following constraints apply to the pivot stage.

Feature	Constraint / Behavior
Max Aggregations	3 functions per pivot stage. When multiple aggregations are used, column names are auto-generated (for example, sum_1_NETWORK).
Max Pivot Values	10 values in the IN clause. This threshold prevents "wide table" performance degradation and avoids exceeding BigQuery column limits.
Optional BY	If the by clause is omitted, the output returns aggregate data only. When used, the by clause is limited to 5 group values to ensure query stability.
Data Types	Primitive types only for the pivot key. The FOR expression must resolve to a primitive data type. JSON fields and Records cannot be used as the pivot key — they must be cast to STRING in a previous stage as a workaround.

Note: Not all XQL aggregation functions are supported in pivot. Functions such as first, last, least, and median are not supported. See the Parameters section for the full list of allowed aggregation functions.

Supported contexts

Context	Supported
Correlation	No
Parsing Rule	No
Dataset RBAC	No
Scheduled Queries	Yes
Widgets/Reports	Yes

Examples

Example 1: Basic Pivot — Single aggregation using the BY clause

Goal: Sum bytes transferred per host, pivoted across event types.

XQL code:

dataset = xdr_data
| pivot sum(bytes_transferred) for event_type IN ("NETWORK", "FILE", "PROCESS") by host
| limit 100

Explanation: This query takes the bytes_transferred field, applies a sum aggregation, and pivots the results so that each distinct event_type value (NETWORK, FILE, PROCESS) becomes its own column. The by host clause groups the output by host.

Starting dataset (xdr_data):

_id	host	event_type	bytes_transferred
1	STORY	NETWORK	1024
2	STORY	NETWORK	2048
3	STORY	FILE	512
4	STORY	PROCESS	256
5	EVENT_LOG	NETWORK	4096
6	EVENT_LOG	FILE	768
7	EVENT_LOG	PROCESS	128
8	INJECTION	NETWORK	2048
9	INJECTION	PROCESS	64

Output:

host	NETWORK	FILE	PROCESS
STORY	3072	512	256
EVENT_LOG	4096	768	128
INJECTION	2048	null	64

Example 2: Pivot without a BY clause

Goal: Count events by type without grouping by any other field.

XQL code:

dataset = xdr_data
| pivot count(event_type) for event_type IN ("NETWORK", "FILE", "PROCESS", "REGISTRY")
| limit 100

Explanation: When the by clause is omitted, the output contains only the aggregated data across the IN values — a single row with one column per pivot value.

Before pivot:

event_type	count(event_type)
NETWORK	5420
FILE	1893
PROCESS	3102
REGISTRY	764

Output:

NETWORK	FILE	PROCESS	REGISTRY
5420	1893	3102	764

Example 3: Using pivoted columns in subsequent stages

Goal: Pivot event counts by host, then filter and sort the results.

XQL code:

dataset = xdr_data
| pivot count(event_type) for event_type IN ("NETWORK", "FILE", "PROCESS") by agent_hostname
| filter NETWORK > 100
| sort desc NETWORK
| fields agent_hostname, NETWORK, FILE, PROCESS
| limit 50

Explanation: After the pivot stage, the newly created columns (NETWORK, FILE, PROCESS) become regular fields in the dataset. They can be referenced in any subsequent stage — filter, sort, fields, etc. — just like any other column. Here, the query filters for hosts with more than 100 network events and sorts by the NETWORK column in descending order.

After pivot:

agent_hostname	NETWORK	FILE	PROCESS
host-alpha	250	80	410
host-beta	150	42	310
host-gamma	88	17	205
host-delta	12	5	55

After filter (final output):

agent_hostname	NETWORK	FILE	PROCESS
host-alpha	250	80	410
host-beta	150	42	310

Example 4: Multiple aggregations in a single pivot

Goal: Use two aggregate functions — sum and max — in one pivot statement.

XQL code:

dataset = xdr_data
| pivot sum(bytes_transferred), max(bytes_transferred) for event_type IN ("NETWORK", "FILE") by host
| limit 100

Explanation: This query applies both sum and max aggregations to bytes_transferred, pivoted by event_type. When multiple aggregation functions are used, the output column names follow the pattern <function>_<index>_<value> (for example, sum_1_NETWORK, max_2_FILE).

Starting dataset (xdr_data):

_id	host	event_type	bytes_transferred
1	host-alpha	NETWORK	1024
2	host-alpha	NETWORK	3072
3	host-alpha	FILE	512
4	host-alpha	FILE	768
5	host-beta	NETWORK	2048
6	host-beta	FILE	256

Output:

host	sum_1_NETWORK	sum_2_FILE	max_1_NETWORK	max_2_FILE
host-alpha	4096	1280	3072	768
host-beta	2048	256	2048	256

Example 5: Pivot with two BY fields

Goal: Group the pivoted output by two fields to produce a more granular breakdown.

XQL code:

dataset = xdr_data
| pivot sum(bytes_transferred) for event_type IN ("NETWORK", "FILE") by host, source_zone
| limit 100

Explanation: This query pivots bytes_transferred by event_type and groups the results by both host and source_zone, producing a more detailed breakdown.

Starting dataset (xdr_data):

_id	host	source_zone	event_type	bytes_transferred
1	host-alpha	DMZ	NETWORK	1024
2	host-alpha	DMZ	NETWORK	512
3	host-alpha	Internal	NETWORK	2048
4	host-alpha	Internal	FILE	768
5	host-beta	DMZ	NETWORK	4096
6	host-beta	DMZ	FILE	256
7	host-beta	Internal	NETWORK	1536

Output:

host	source_zone	NETWORK	FILE
host-alpha	DMZ	1536	null
host-alpha	Internal	2048	768
host-beta	DMZ	4096	256
host-beta	Internal	1536	null

Related articles

• Stages: comp, fields, filter, transpose
• Functions: count, sum, min, max