The Pivot transform allows for the creation of new columns and transposing data into a new layout. It can be used to make results more compact by pivoting the input data on a column value.
The Pivot transform requires 1 input transform that has at least 3 columns.
For example, the input could be a SQL Select transform that corresponds to the following SQL Server table:
In the Pivot configuration dialog, you must configure the following settings:
- Grouping Element - The column(s) you want to group multiple rows together on (e.g. the Source column(s) from the SearchResults table). The selected column(s) will be part of the output as well.
- Pivot Element - The column containing values that will be used to create new columns in the output (e.g. the Type column in the SearchResults table). For each unique pivot element value, there will be a corresponding new output column.
- Value Element - The column containing values that will be used as values of the new output columns (e.g. the Value column in the SearchResults table).
- Automatically Generate Column Names - [New in V2.5] This will automatically generate a comma-delimited string using all unique values on your Pivot Element column which will be used as the names of the new columns.
You will see the auto-generated comma-delimited string only after you close and re-open the configuration dialog.
- Sort The Automatically Generated Column Names - Sorts the new columns but this is done only the first time when the columns are created.
- The names of the new columns - If you prefer not to use the Automatically Generate Column Names feature, you can manually type-in the comma-delimited list containing the names of the new columns the Pivot transform will generate. Each of the names should match a value from the Pivot Element column (e.g. Hits,Unique IPs).
The output of the Pivot transform consists of rows grouped by the Grouping Element column.
The output also contains new columns that correspond to the values of the Pivot Element column.
The values within the new columns come from the Value Element column.
The figure below illustrates the output from the Pivot transform as applied to the SearchResults table.