Skip to main content
The Transpose Rows transformation component lets you combine multiple input rows into a single output row. One row will be produced for each value of the columns specified in the Groupings parameter. This is especially helpful when you want to restructure long datasets into a wide format for reporting, comparisons, or aggregations. The output of this component differs slightly depending on your cloud data warehouse environment:
  • In a Snowflake environment, this component concatenates each value into a delimited string with the format [ value, value, value ]. 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 a Databricks environment, this component returns values as Databricks ARRAY <String> types. These values can’t be manipulated by a Split Field component.
  • In Amazon Redshift and Google BigQuery environments, this component concatenates each value into a delimited string with the format value,value,value. 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.
  • For an alternative approach to transforming rows into columns, consider the Pivot component instead.
  • If you want to map columns from the input dataset into rows in the output data, use the Transpose Columns component.

Use case

This component can be used to restructure long datasets into a wide format for reporting, comparisons, or aggregations. Some typical uses of this are:
  • Pivoting long-form time-based data into a wide format for period-over-period comparisons.
  • Condensing repeated rows (for example, in survey responses or log files) into a single record.

Properties

string
required
A human-readable name for the component.
object selector
required
The input column to aggregate.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.
object selector
Select columns to sort rows by within each grouping. This determines the order that values appear in the transposed output. When sorting by multiple columns, sorting is applied in the order you select the columns.Leave this blank to transpose rows without sorting.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.
object selector
Select the source columns to form the groupings. The output flow will contain one row for each distinct combination of values within the grouping columns.To use grid variables, toggle Use Grid Variable on. For more information, read Grid variables.

Example

This example uses a Snowflake project. Other cloud data warehouses will give slightly different output formats.
We have a table of products and yearly sales information. However, the format gives us a lot of repeated data:
We want to transpose the data into a condensed format that puts the data for each product into a single row:
To achieve this transformation, we connect the input table to a Transpose Rows component, which we will configure as follows:
  • Aggregations: year, sales
  • Sort Order: Left blank. In a long dataset, sorting on product_id might be appropriate.
  • Groupings: product_id