Tip - De-normalize data/pivot rows into columns

HOW TO: De-normalize data/pivot rows into columns

Problem Description:

The Normalizer transformation is used to "normalize" data, or to pivot columns into rows. How can you do the opposite (de-normalize, denormalize), or pivot multiple rows into multiple columns in a single row? Suppose you have a source table with this data that is a record of monthly expenses for each of your Sales Reps:

Source Data
SALES_ID MONTH AMOUNT
1 JAN 100
1 FEB 150
1 MAR 75
...
1 DEC 200
2 JAN 120
2 FEB 250
2 MAR 125

You want to de-normalize this data into this structure:

Target Data
SALES_ID JAN_AMT FEB_AMT MAR_AMT
1 100 150 75
2 120 250 125

Solution:

To accomplish this do the following:
1. Create an Aggregator transformation with the following ports and expressions:
NAME IN/OUT EXPRESSION GROUP BY
SALES_ID IN YES
MONTH IN NO
AMOUNT IN NO
JAN_AMT OUT FIRST(AMOUNT, MONTH='JAN')
FEB_AMT OUT FIRST(AMOUNT, MONTH='FEB')
MAR_AMT OUT FIRST(AMOUNT, MONTH='MAR')
APR_AMT OUT FIRST(AMOUNT, MONTH='APR')
2. Connect the SALES_ID, MONTH and the AMOUNT ports from the Source Qualifier to the Aggregator.
3. Connect the JAN_AMT, FEB_AMT, MAR_AMT, etc. ports to the target

Applies To:

PowerCenter