Transpose Rows

Transpose Rows

This component is essentially the opposite of the Transpose Columns component. Multiple rows are combined into a single output row (one for each value of the columns specified in the "Groupings" parameter). Each row in the output holds the aggregated data in an array.

If required, you can use a Split Field component after a Transpose Rows component to turn the delimited strings into a set of new columns.

 

Properties

Property Setting Description
Name Text The descriptive name for the component.
Aggregations Source Column The input column to transpose.
Sort Order Columns The rows within the grouping are ordered by these columns before being put into the strings. If you are transposing multiple columns, ordering will ensure the items are all in a consistent order - see the example.
Groupings Columns 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.