Group rows by key into one row with more columns

[Anonymous]

Hi there,

I need to group 3 rows with the same key into 1 row.

I have in entry for example : 

Key; Counter; Value

1;FH;100

1;CY;200

1;FC;600

and in my output

Key;Counter_FH;Counter_CY;Counter_FC

1;100;200;600

Do we have in Talend a component to group multiple rows and then I could say in each colum "Counter.equals("FH") ? Value : null"

Or something like that ?

Thanks (I wanted to try with tAggregate but I don't want to use function so i can't ?) 

[manodwhb]

@samsamarzr ,this is the one way.

you can do with tmap with "Counter.equals("FH") ? Value : null" .

[Anonymous]

Sorry but that's not what I wanted

Here you have 1 column value and I want 3 column value in my output

One for the FH counter, one for the FC counter and one for the CY counter

I really need to group rows into one row and then I think I can handle the problem with some if condition

If I use only tMap I'll have 3 rows not one because I have 3 rows in my input.

[Anonymous]

Just do it with SQL.

select 
    key,
    MAX(CASE Counter WHEN 'FH' THEN Value ELSE 0 END) AS Value1,
    MAX(CASE Counter WHEN 'CY' THEN Value ELSE 0 END) AS Value2,
    MAX(CASE Counter WHEN 'FC' THEN Value ELSE 0 END) AS Value3
from tablename
group by key
;

[manodwhb]

@samsamarzr ,check this one.

[Anonymous]

Thanks for the help, I think I've found a solution, because there are other case to care about, I'll post the solution later and explain.

thanks