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 and Limitations).

Syntax

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

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:

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:

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:

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:

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.

  • Stages: pivot, comp, fields, sort