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
```sql
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](#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](#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**:
```sql
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**:
```sql
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**:
```sql
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**:
```sql
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**:
```sql
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`