Split the non-key columns to separate tables with key column in both / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.
source table: ID is the key column, Name and Phone No are non-key columns
ID | Name | Phone No |
10 | AAA | 123 |
20 | BBB | 234 |
30 | CCC | 434 |
40 | DDD | 343 |
50 | EEE | 442 |
Target Table 1:
ID | Name |
10 | AAA |
20 | BBB |
30 | CCC |
40 | DDD |
50 | EEE |
Target Table 2:
ID | Phone No |
10 | 123 |
20 | 234 |
30 | 434 |
40 | 343 |
50 | 442 |
Solution:
Step 1: Source qualifier: get the source table to the mapping area. See image below.Step 2: Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect aggregator transformation with each of the expression transformation as follows.Step 3: We need another set of aggregator to be associated with each of the expression tranformation from the previous step.Step 4: In the final step connect the aggregators with the two target tables as follows.Here is the iconic view of the entire mapping.Comments are appreciated
No comments:
Post a Comment