Pivot data in Talend

问题:

I have some data which I need to pivot in Talend. This is a sample:

brandname,metric,value
A,xyz,2
B,xyz,2
A,abc,3
C,def,1
C,ghi,6
A,ghi,1

Now I need this data to be pivoted on the metric column like this:

brandname,abc,def,ghi,xyz
A,3,null,1,2
B,null,null,null,2
C,null,1,6,null

Currently I am using tPivotToColumnsDelimited to pivot the data to a file and reading back from that file. However having to store data on an external file and reading back is messy and unnecessary overhead.

Is there a way to do this with Talend without writing to an external file? I tried to use tDenormalize but as far as I understand, it will return the rows as 1 column which is not what I need. I also looked for some 3rd party component in TalendExchange but couldn't find anything useful.

Thank you for your help.

回答1:

Assuming that your metrics are fixed, you can use their names as columns of the output. The solution to do the pivot has two parts: first, a tMap that transposes the value of each input-row in into the corresponding column in the output-row out and second, a tAggregate that groups the map's output-rows according to the brandname.

For the tMap you'd have to fill the columns conditionally like this, example for output colum named "abc": out.abc = "abc".equals(in.metric)?in.value:null

In the tAggregate you'd have to group by out.brandname and aggregate each column as sum ignoring nulls.