How to pivot or unpivot a table

If you want to pivot or unpivot a database table in xDI Designer, there are two ways of accomplishing these tasks:

Using a query

If your database can pivot or unpivot tables natively, you can write a query that performs the transformation and reverse it in Designer.

Here is an example of the task using the Microsoft SQL pivot operator.

  1. Write a query that gives you the desired transformed table in MS SQL:

    MS SQL Query

  2. In the MS SQL metadata, create a query folder as well as a query element. Give the element a name, and paste the query.

    Query in metadata

  3. Save the metadata.

  4. Open the query element’s context menu, and choose Actions > Reverse.

    Reverse query

  5. Save the metadata again.

  6. Use the query’s result as a source in your mappings:

Reversed query in a mapping

Using stages

In all other cases, you can do pivot or unpivot transformations using stages.

If you do not have an available database to hold a stage, you can use a built-in HSQL database.

Example of a pivot transform

This example starts with a source table that holds information categorized by calendar quarters. The table has multiple lines per quarter, but it needs to be transformed to have a single line per quarter with multiple columns to hold all related values.

This is the source table:

Source unpivoted table

To transform the data, start by adding four stages to your mapping. Feed each stage with values from a single quarter, using a filter.

Pivot mapping

Join the stages so you can use them to feed the columns of the target table:

Pivoted target table

Example of an unpivot transform

This example works in the other direction. It starts with a source table that holds information categorized by calendar quarters, but it has a single line per quarter. It needs to be transformed to have multiple lines per quarter so each related value has its own line.

For this, you can use one stage with four sets:

Unpivot mapping

Each set represents a quarter with a hard-coded QNO value, and the corresponding value mapped from the source table. Combine all sets with the UNION operator, and feed the target table.