> 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/transpose.md).

# transpose

Use the `transpose` stage to turn rows into columns. Each input row becomes a separate output column, effectively rotating the dataset from a columnar (wide) orientation to a row-based (tall) orientation, converting column headers into data values. This is useful for transforming vertically oriented data into a horizontal layout, making it easier to compare field values across rows side by side.

To maintain system reliability and avoid unpredictable SQL state mutations, the `transpose` stage enforces several constraints, most notably that it must be the terminal stage of the pipeline (see [Usage notes](#usage-notes) and [Limitations](#limitations)).

## Syntax

```sql
transpose [<max_rows>] [column_name=<string>] [header_field=<field>] [include_empty=<bool>]

```

## Parameters

| Name            | Type    | Required | Description                                                                                                                                                                       |
| --------------- | ------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `max_rows`      | integer | Yes      | The maximum number of input rows to transpose into columns. The maximum allowed value is **50**. If the user provides a value larger than 50, the number of rows is capped at 50. |
| `column_name`   | string  | No       | The name of the output field that contains the original input field names. Default is `"column"`. In the output, this field appears as `field_name`.                              |
| `header_field`  | string  | No       | The name of an input field whose values are used as the output column names. If not provided, output columns are named `"value_1"`, `"value_2"`, and so on.                       |
| `include_empty` | boolean | No       | If set to `false`, excludes any input field (column) that had no values across all transposed rows. Default is `true`.                                                            |

## Returns

The `transpose` stage returns a transformed dataset where:

* Each input row becomes a separate output column.
* A new field (named by `column_name`, default `"field_name"`) is added, containing the original input field names.
* Output column names are derived from the `header_field` values if specified, or default to `"value_1"`, `"value_2"`, etc.
* The number of transposed rows is limited by the `max_rows` parameter (default `5`, maximum `50`).
* If `include_empty` is `false`, fields that had no values across all transposed rows are excluded from the output.
* **All transposed values are cast to `STRING`** as the universal data type, regardless of their original type. This ensures the transpose operation succeeds across diverse datasets with mixed column types.
* **Null padding**: If the source data contains fewer rows than the specified `max_rows` limit (for example, `transpose 50` called on a 30-row result set), the remaining value columns are automatically filled with `NULL` values to maintain a consistent output structure.

## Usage notes

* **Terminal stage enforcement**: The `transpose` stage must be the **last stage** in the XQL pipeline. No subsequent stages (such as `filter`, `comp`, `join`, or `union`) are allowed after `transpose`. This is because transposing data fundamentally alters the schema — column headers become data and vice versa — making it impractical to apply further transformations on the dynamically generated schema.
* The `transpose` stage is a "blocking" operation; it must process all input records before producing any output.
* You must limit the number of rows to transpose using the `max_rows` parameter, up to a maximum of **50 rows**.
* In **Cortex XSOAR**, you must use a limit keyword. For example:

```sql
xdr-xql-generic-query query="dataset = management_auditing | pivot count(management_auditing_result) for management_auditing_type in (\"USER\", \"AUTH\") by management_auditing_severity" max_fields="20" query_name="TestPivotQuery" parse_result_file_to_context="false"
```

or

```sql
xdr-xql-generic-query query="dataset =management_auditing | limit 10 | transpose 2" query_name="TestAniQuery"
```

* The `column_name` parameter controls the label of the field that holds the original field names in the transposed output.
* When `header_field` is specified, the values of that field in each input row are used as the names of the corresponding output columns, making the output more descriptive.
* The `transpose` stage is typically used after aggregation stages (such as `comp`) or data source stages (such as `dataset`) to reshape summary data for display or comparison.
* **No ordering guarantee**: The order of the output data is based on the previous stage output. If a specific order is needed, use a `sort` stage before `transpose`.
* **Graphs are not supported** for transposed output.

## Limitations

| Feature            | Constraint / Behavior                                                                                              |
| ------------------ | ------------------------------------------------------------------------------------------------------------------ |
| **Terminal Stage** | Must be the last stage in the XQL pipeline. No subsequent `filter`, `comp`, `join`, or `union` stages are allowed. |
| **Max Rows**       | 50 rows maximum. The user can provide their own smaller row count limit via the `max_rows` argument.               |
| **Null Padding**   | If source data has fewer rows than the specified limit, remaining value columns are filled with `NULL`.            |
| **Data Typing**    | All transposed values are cast to `STRING` as the universal data type.                                             |
| **Ordering**       | No ordering guarantee. Data order is based on previous stage output.                                               |
| **Graphs**         | Graphs are not supported for transposed output.                                                                    |

## Supported contexts

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

## Examples

### Example 1: Basic transpose

**Goal**: Transpose a small result set of selected fields.

**XQL code**:

```sql
dataset = xdr_data
| fields id, severity, custom_fields
| transpose 5

```

**Explanation**: This query selects three fields and transposes up to 5 rows. Each original field name appears in the `field_name` column, and each row's values become separate output columns (`value_1` through `value_5`). Since there are only 3 source rows but the limit is 5, the remaining columns (`value_4`, `value_5`) are filled with `NULL`.

**Before `transpose` (3 rows × 3 columns):**

| id        | severity | custom\_fields         |
| --------- | -------- | ---------------------- |
| `evt-001` | `5`      | `{"priority": "high"}` |
| `evt-002` | `3`      | `{"priority": "low"}`  |
| `evt-003` | `8`      | `null`                 |

**After `transpose` (3 rows × 6 columns):**

| field\_name     | value\_1                 | value\_2                | value\_3    | value\_4 | value\_5 |
| --------------- | ------------------------ | ----------------------- | ----------- | -------- | -------- |
| `id`            | `"evt-001"`              | `"evt-002"`             | `"evt-003"` | `null`   | `null`   |
| `severity`      | `"5"`                    | `"3"`                   | `"8"`       | `null`   | `null`   |
| `custom_fields` | `"{"priority": "high"}"` | `"{"priority": "low"}"` | `null`      | `null`   | `null`   |

> **Note:** All values are cast to `STRING` in the transposed output, regardless of their original data type.

### Example 2: Transpose with custom row limit

**Goal**: Transpose the first 20 rows into columns.

**XQL code**:

```sql
dataset = xdr_data
| fields src_ip, event_type
| transpose 20

```

**Explanation**: This query transposes up to 20 input rows into columns. Each transposed row becomes a column named `"value_1"` through `"value_20"`, and the original field names appear in the `field_name` column. If fewer than 20 rows exist in the input, the remaining columns are padded with `NULL`.

### Example 3: Transpose with custom column name and header field

**Goal**: Transpose rows using a custom label for the field name column and use the `sourcetype` field values as output column names.

**XQL code**:

```sql
dataset = xdr_data
| fields sourcetype, event_count, severity
| transpose column_name="Test Name" header_field=sourcetype include_empty=false

```

**Explanation**: This query transposes the input rows, placing the original field names into a column called `"Test Name"` instead of the default `"field_name"`. The values of the `sourcetype` field in each input row are used as the names of the output columns. Any fields that have no values across all transposed rows are excluded because `include_empty` is set to `false`.

**Output:**

| Test Name    | syslog | firewall | endpoint |
| ------------ | ------ | -------- | -------- |
| event\_count | 150    | 42       | 5        |
| severity     | low    | high     | medium   |

### Example 4: Transpose after aggregation

**Goal**: Aggregate event counts by type, then transpose the summary for a horizontal comparison.

**XQL code**:

```sql
dataset = xdr_data
| comp count(event_id) as event_count by event_type
| transpose 10

```

**Explanation**: This query first aggregates the data to count events by type, then transposes the result so each event type's row becomes a column. This is useful for creating a horizontal summary view of aggregated data. Since `transpose` must be the terminal stage, no further processing is applied after it.

## Related articles

* **Stages**: [`pivot`](broken://pages/8a83089b0bf7b0b470023882e568fe665d19cad3), [`comp`](broken://pages/2ee1008c0de5374474bfacad85048f4db4feee24), [`fields`](broken://pages/bdb63437f61964bc97657f9631ba8c352f887356), [`sort`](broken://pages/b5ae6d3e3910ec3da69b8087d5c8e17021042d14)


---

# 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/transpose.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.