הצטרפו לקבוצות שלנו לקבלת עדכונים מרוכזים פעם בשבוע:

ווטסאפ:
http://wa.dwh.co.il
טלגרם:
http://telegram.dwh.co.il

Tip - De-normalize data/pivot rows into columns

More
12 years 10 months ago #6691 by Or Pelach
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

Please התחברות to join the conversation.

Moderators: Laromme Barr
Time to create page: 0.260 seconds