Transpose Rows
Transpose Rows is a transformation component that enables users to combine multiple rows into a single output row (one row for each value of the columns specified in the Groupings parameter).
In Snowflake environments, this component concatenates each value into a delimited string in the output. You could then use a Split Field component after the Transpose Rows component to convert the delimited strings into a set of new columns, if required.
In Databricks environments, this component returns values as Databricks ARRAY <String> types. Note that these values can't be manipulated by a Split Field component as described above for Snowflake.
Note
Use the Transpose Columns component if you want to map sets of input columns into new output columns.
Properties
Name = string
A human-readable name for the component.
Aggregations = dual listbox
The input column to aggregate.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Aggregations dialog.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you're performing multiple transpositions, ordering will ensure that the items are in a consistent order.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Sort Order dialog.
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.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Groupings dialog.
Name = string
A human-readable name for the component.
Aggregations = dual listbox
The input column to aggregate.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Aggregations dialog.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you're performing multiple transpositions, ordering will ensure that the items are in a consistent order.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Sort Order dialog.
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.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Groupings dialog.
Name = string
A human-readable name for the component.
Aggregations = dual listbox
The input column to aggregate.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Aggregations dialog.
Sort Order = dual listbox
Rows included in the "groupings" are ordered using this Sort Order parameter. If you're performing multiple transpositions, ordering will ensure that the items are in a consistent order.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Sort Order dialog.
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.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Groupings dialog.
Delimiter = string
A delimiting character used to separate concatenated values. The default is a comma.
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).
Click the Text Mode toggle at the bottom of the Null Replace dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.
To use grid variables, tick the Use Grid Variable checkbox at the bottom of the Null Replace dialog.
Distinct Aggregations = Boolean
When "Yes", any duplicates from the selected aggregation columns are removed. Default setting is "No".
| Snowflake | Databricks | Amazon Redshift |
|---|---|---|
| ✅ | ✅ | ✅ |