Scenario 2:

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


Step  1: Source qualifier: get the source table to the mapping area. See image below.

scenario 2 src def to src qualifier

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.

scenario 2 agg to expression

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.

scenario 2 agg to tgt

Here is the iconic view of the entire mapping.

scenario 2 iconic view of mapping


Comments are appreciated