> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/pivot.md).

# 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

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/pivot.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.