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-reference), [`comp`](broken-reference), [`fields`](broken-reference), [`sort`](broken-reference)