.png){kind=logo}
Array Aggregate
-
DarkLight
Array Aggregate
-
DarkLight
This article is specific to the following platforms - Snowflake.
Transpose Rows Component
The Transpose Rows component enables users to combine 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.
Where required, users can use a Split Field component after the Transpose Rows component to convert the delimited strings into a set of new columns.
Users can also leverage the transformation capabilities of the Transpose Columns component.
Snowflake Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | String | A human-readable name for the component. |
| Aggregations | Column Select | The input column to aggregate. |
| Sort Order | Column Select | 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 | Column Select | One or more source columns that form the groupings. The output will have one row for every combination of grouping column values. |
Strategy
This generates an aggregate query using the ARRAY_AGG function.
Example
This job takes a table of all data on all US airports, and creates comma-separated arrays of US airport codes and airport names, grouped by State.
Groupings and aggregations are set. IATA (airport code) is set as the Sort Order, so that the airport codes and airport names will be output in the same order.
The sample data shows that where there are multiple airports in a state, they are aggregated into a single output row, each containing a single array.
