Transpose Rows
The Transpose Rows component combines multiple rows into a single output row (one row for each value of the columns specified in the Groupings parameter). The component concatenates each value into a delimited string in the output.
You can also use a Split Field component after the Transpose Rows component to convert the delimited strings into a set of new columns.
Note
Use the Transpose Columns component if you want to map sets of input columns into new output columns.
Note
If you're using Snowflake, use the Array Aggregate component instead.
Properties
Name = string
A human-readable name for the component.
Aggregations = dual listbox
The input column to aggregate.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you are performing multiple transpositions, ordering will ensure that the items are in a consistent order.
Groupings = dual listbox
One or more source columns that form the groupings.
The output will have one row for every combination of grouping column values.
Name = string
A human-readable name for the component.
Groupings = dual listbox
One or more source columns that form the groupings.
The output will have one row for every combination of grouping column values.
Aggregations = dual listbox
The input column to aggregate.
Delimiter = string
A delimiting character used to separate concatenated values. The default is a comma.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you are performing multiple transpositions, ordering will ensure that the items are in a consistent order.
Null Replace = column editor
- Field: Select a field from the drop-down menu. This field will have any NULL values replaced by a value the user specifies in the "Replacement" column of this parameter. This may be useful for retaining the order of elements, since otherwise NULL values will not be included.
- Replacement: The value used to replace NULL values in the corresponding field (row).
Distinct Aggregations = drop-down
When "Yes", any duplicates from the selected aggregation columns are removed. Default setting is "No".
Name = string
A human-readable name for the component.
Groupings = dual listbox
One or more source columns that form the groupings.
The output will have one row for every combination of grouping column values.
Aggregations = dual listbox
The input column to aggregate.
Delimiter = string
A delimiting character used to separate concatenated values. The default is a comma.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you are performing multiple transpositions, ordering will ensure that the items are in a consistent order.
Null Replace = column editor
- Field: Select a field from the drop-down menu. This field will have any NULL values replaced by a value the user specifies in the "Replacement" column of this parameter. This may be useful for retaining the order of elements, since otherwise NULL values will not be included.
- Replacement: The value used to replace NULL values in the corresponding field (row).
Name = string
A human-readable name for the component.
Groupings = dual listbox
One or more source columns that form the groupings.
The output will have one row for every combination of grouping column values.
Aggregations = dual listbox
The input column to aggregate.
Delimiter = string
A delimiting character used to separate concatenated values. The default is a comma.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you are performing multiple transpositions, ordering will ensure that the items are in a consistent order.
Null Replace = column editor
- Field: Select a field from the drop-down menu. This field will have any NULL values replaced by a value the user specifies in the "Replacement" column of this parameter. This may be useful for retaining the order of elements, since otherwise NULL values will not be included.
- Replacement: The value used to replace NULL values in the corresponding field (row).
Strategy
This generates an aggregate query using the LISTAGG function.
| Snowflake | Delta Lake on Databricks | Amazon Redshift | Google BigQuery | Azure Synapse Analytics |
|---|---|---|---|---|
| ❌ | ✅ | ✅ | ✅ | ✅ |