Working with crosstab transformations

You use crosstab transformations to return aggregated data from a selected column across other columns.

About this task

Apply crosstab transformations to display the relations between the values in different columns by transforming one of the columns into a new row. When configuring a crosstab transformation, you define one or a few columns for the side group and one column for the top group. Then, you select an aggregated column to provide values for the newly formed fields in the intersections of top group and side group values.

For example, you have a table with data on production of a number of materials in different regions.

Table 1. Original result set data
PRODUCT QTY REGIONID
Plywood 485 1
Plywood 712 2
Plywood 787 3
Lumber 959 1
Lumber 2372 2
Lumber 2360 3
Siding 1794 1
Siding 1940 2
Siding 739 3
Industrial Panels 1225 1
Industrial Panels 627 2
Industrial Panels 934 3

You want to cross-tabulate the QTY column data so that the aggregated values are related to the type of product and region number. You select and group the PRODUCT column as a side group column that will provide the rows and the REGIONID as a top group column to provide the columns. The aggregated values of the QTY column will be distributed across the fields that are formed by the intersections of the top group and the side group column values. As a result, you get the following table:

Table 2. Crosstab result set data
PRODUCT QTY(1) QTY(2) QTY(3)
Industrial Panels 1225 627 934
Lumber 959 2372 2360
Plywood 485 712 787
Siding 1794 1940 739
You can use Collate Option for case sensitive or case insensitive comparison of data. The Collate option gets activated when the following two conditions are satisfied:
  • The selected column must be of the type string, var char, or text.
  • Aggregation type should be GROUP.

To configure a crosstab transformation in the analytical query structure:

Procedure

  1. In the Analytical query structure, select a crosstab transformation node.
    In the Item editor pane, you can view the items of the crosstab transformation. The columns from the source data set are displayed in the Group and Crosstab sections.
  2. Optional: If the selected crosstab transformation is not populated yet, add a query or table into the <none> node. For more information, see Populating analytical query transformations.
  3. In the Group section, select check boxes next to the columns that you want to use in the side group. In the example above, this is the PRODUCT column.
    Note: Use the Include All Columns check box to select or deselect all available columns.
  4. In the Aggregation column, click the value and select an aggregation type for the selected column from the list.
  5. Optional: You can configure the grouping of records in case sensitive or case insensitive manner by clicking on the Collate Option button. Specify Collate Option for each group column by selecting case sensitive or case insensitive option from the drop-down provided.
    Note: Collate Option button is enabled when Grouping column is of character data type and Aggregation is of type Group.
  6. From the Grouping column list in the Crosstab section, select a column that will provide unique values to form columns in the top group. In the example above, this is the REGIONID column.
  7. From the Description column list, select a column to provide values for the descriptions for the top group columns. In the example above, this is the REGIONID column.
  8. Optional: You can configure the grouping of columns in case sensitive or case insensitive manner by clicking on the Collate Option button. Specify Collate Option for grouping column by selecting case sensitive or case insensitive option from the drop-down provided.
    Note: Collate Option button is enabled when Grouping column is of character data type.
  9. From the Value column column, select the check box next to the column that will provide the values for the crosstab cells. In the example above, this is the QTY column.
  10. In the Aggregation column, click the value and select an aggregation type for the values in the crosstab cells.
  11. Optional: To add another transformation to the current analytical query structure, follow the procedure described in Building analytical query structures.