List Aggregate

Dark
Light

Article Summary

Share feedback

Thanks for sharing your feedback!

This article is specific to the following platforms - Redshift - BigQuery - Synapse - Delta Lake.

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.

Properties

Redshift Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
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.
Aggregations	Column Select	The input column to aggregate.
Delimiter	String	A delimiting character used to separate concatenated values. The default is a comma.
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.
Null Replace	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.
Null Replace	Replacement	The value used to replace NULL values in the corresponding field (row).
Distinct Aggregations	Select	When "Yes", any duplicates from the selected aggregation columns are removed. Default setting is "No".

BigQuery Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
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.
Aggregations	Column Select	The input column to aggregate.
Delimiter	String	A delimiting character used to separate concatenated values. The default is a comma.
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.
Null Replace	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.
Null Replace	Replacement	The value used to replace NULL values in the corresponding field (row).

Synapse Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
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.
Aggregations	Column Select	The input column to aggregate.
Delimiter	String	A delimiting character used to separate concatenated values. The default is a comma.
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.
Null Replace	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.
Null Replace	Replacement	The value used to replace NULL values in the corresponding field (row).

Delta Lake Properties
Property	Setting	Description
Name	String	A human-readable name for the component.
Aggregations	Column Select	The column(s) to aggregate.
Sort Order	Column Select	Rows included in the Groupings parameter 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 LISTAGG function.

Example

This job creates comma-separated strings of airport codes and airport names, grouped by the state and city.

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 city, they are aggregated into a single output row. Notice that for Anchorage, there are three airports, and the order of the airport names is consistent with the order of the airport codes.