Pivot and aggregate rows

[jcpreaux]

Hello,

I've 3 tMysqlInput components in a job which return these datas :

n°1 :

CustomerID|Pack_1

1|Yes

2|Yes

n°2 :

CustomerID|Pack_2

1|Yes

3|Yes

n°3 :

CustomerID|Pack_3

1|Yes

4|Yes

And i'm trying to get this kind of result :

CustomerID|Pack_1|Pack_2|Pack_3

1|Yes|Yes|Yes

2|Yes||

3||Yes|

4|||Yes

I tried whith tMap component whitout success,  any idea ?

Thank you

JCP

[Anonymous · Accepted solution]

Hi jcpreaux

First, use a tunite to merge the three input data, and then use a tDenormalize to denormalize the second column with a delimiter, finally, extract data from the second coumn to multiple fields with tExtractDelimitedFields, the job looks like:

tMysqlInput1----tUnite--tDenormalize--->tExtractDelimitedFields-->tLogRow

tMysqlInput2----

tMysqlInput3----

Let me know if it works!

Regards

Shong