Want to merge multiple rows into one row

[ahmedshsh]

I have a file that looks like this :

1,Andrew
1,Beer
1,USA
2,Nadine
2,Mark
2,UK
3,Bian
3,Mendez
3,USA

I want to transform to merge rows with the same number to a single row so that the file would look like this:

Andrew,Beer,USA
Nadine,Mark,UK
Bian,Mendez,USA

What stage should I use ?

[chulett]

Welcome. :D

Transformer with stage variables could be used but you'd need to append a trailer record to the file before processing so you can recognize EOF and push out the last group. And the stage variables would be doing 'group change detection' and appending field values per number.

Another approach is to use a hashed file. From a transformer both uncached lookup and write to the same hashed file keyed on the number. No hit? Insert the key and field. Hit? Append the new field to the existing field with a comma separator. Dump the hashed file when complete and leave the key behind. Note that input order would not be preserved unless you did something to explicitly preserve it - account based hashed file with a sorted select, dump the key with the data and then sort based on the key, etc.

I'm sure there are other ways.