Array Aggregate

Dark
Light

Article Summary

Share feedback

Thanks for sharing your feedback!

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.