Denormalize (pivot) transformation

When to use this transformation

This transformation pivots data from multiple rows into columns in a single row.

Parameters

• Columns to Group By: a comma-separated list of columns to group by. This parameter is a required field for the transformation.
• Columns to Include: a comma-separated list of columns to include. For example: firstName, lastName, ssn, age, address, phone. address and phone are specified as the only columns to include. Then, ssn and age will be included in the final dataset.
• Columns to Exclude: a comma-separated list of columns to exclude. For example: firstName, lastName, ssn, age, address, phone. age and ssn are specified as columns to exclude. Then, ssn and agewill not be included in the final dataset.
• Leading Column: the name of the leading column. The leading column sets the pattern for the other columns. For example, if the Address is a leading column, and there are 2 addresses for the given first+last name, but there are 4 phones, the phones will be included in the final dataset 2 times as well.
• Max # of Columns: the maximum number of columns permitted. Use this parameter to set a limit of how many times denormalized columns can be repeated. For example, if there are 4 addresses for the given first + last name and the limit is set to 2 - only address1 and address2 will be included in the final dataset.

Example

With Columns to Group By set to id:

Before

id,address,phone
1,main stree1,412111111
1,anderson dr,412111112
2,home,
3,home,home phone
3,work,work phone
3,,mobile phone

After

id,address,address_2,address_3,phone,phone_2,phone_3
1,main stree1,anderson dr,,412111111,412111112,
2,home,,,,,
3,home,work,,home phone,work phone,mobile phone

Process

To configure Denormalize transformation, go to Transformation/ MAPPING/ Complex Transformations/ Denormalize dataset.