Transpose Columns
    • Dark
      Light

    Transpose Columns

    • Dark
      Light

    Article Summary

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

    Transpose Columns

    Map sets of input columns into new output columns. This effectively performs an unpivot on the data.

    This component re-shapes data by outputting multiple rows for each individual input row. Each set of input columns are mapped to an output column. The output rows are labelled to determine which column the value originated from.

    This is perhaps best understood by the example provided.

    Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    Ordinary ColumnsSelectChoose the ordinary columns, those that are not going to be transposed but are still required in the output. These are effectively a set of grouping columns which are passed to the output unchanged.
    Row Label NameTextProvide the name of a new column name here. It will contain constants you enter into the Column to Row Mapping which identify the original column that the new row originated from.
    Output ColumnsNameA new column name to hold the output of multiple input columns.
    TypeSpecify the data type for the column. This is Text, Numeric or Datetime, and should be compatible with all input columns that will be mapped into this column.
    This is used to validate that the input columns all conform to the type of the output column.
    Column to Row MappingRow Label NameThis editor column will actually appear as the label provided in Row Label Name. Enter an identifier to specify what the rows represent - see the example for details.
    Output Column-1Each defined output column will appear as a column in this mapping. Add a row to this grid for each input column you want to map into an output column.
    Output Column-nAs above, if you are mapping multiple sets of input columns.
    When you map data into multiple output columns, there should be a set of similar input columns for each output column. For example, you may have a set of input columns for each quarterly revenue amount, and another set of input columns for quarterly profits. We could add 4 mappings, one for each quarter, into two target variables Revenue and Profit. This works because there are the same number of input columns per output column.

    Strategy

    Generates a select for each input column, and unions them all together.


    Example

    Here we have 4 quarterly forecasts per each year, forecasting both sales revenue and profit.

    The input data (Budgets in the above screenshot) looks like this:

    However, the data structure we require is given by the columns Year, Quarter, Revenue and Profit. Transpose Columns reshapes the data to provide the data in this format.

    The ordinary columns that just go to the output is the year. We will label the new rows Quarter, since the original data uses multiple columns to represent quarters. We define the new output columns as Revenue and Profit.

    The Column to Row Mapping provides 4 output rows per input row, one for each quarter. An input column must be provided for each of the output columns and for each of the output rows.

    The produces the output we require.



    Video