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.

Transform - Pivot
Transform - Pivot

1. Input

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:

Input Data: SearchResults SQL table
Input Data: SearchResults SQL table

2. Configure

In the Pivot configuration dialog, you must configure the following settings:

Pivot transform configuration dialog
Pivot transform configuration dialog

  1. 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.
  2. 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.
  3. 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).
  4. 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.

    Automatically Generate Column Names
    Automatically Generate Column Names

    You will see the auto-generated comma-delimited string only after you close and re-open the configuration dialog.

  5. Sort The Automatically Generated Column Names - Sorts the new columns but this is done only the first time when the columns are created.
  6. 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).

3. Output

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.

Pivot - sample output
Pivot - sample output

4. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri