topic Re: Group rows by key into one row with more columns in Talend Studio

Group rows by key into one row with more columns

Anonymous — Tue, 17 Sep 2019 10:30:17 GMT

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 ?)

Re: Group rows by key into one row with more columns

manodwhb — Tue, 17 Sep 2019 10:41:16 GMT

@samsamarzr ,this is the one way.

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

Re: Group rows by key into one row with more columns

Anonymous — Tue, 17 Sep 2019 12:09:58 GMT

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.

Re: Group rows by key into one row with more columns

Anonymous — Tue, 17 Sep 2019 12:54:01 GMT

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
;

Re: Group rows by key into one row with more columns

manodwhb — Tue, 17 Sep 2019 12:54:38 GMT

@samsamarzr ,check this one.

Re: Group rows by key into one row with more columns

Anonymous — Tue, 17 Sep 2019 12:56:58 GMT

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