Talend ETL - Combine multiple rows to one comma-separated value

Here you will learn "How to combine multiple rows to one comma-separated value  by using tDenormalize processing component?" in Talend Open Studio.

tDenormalize processing component helps synthesize the input flow means denormalizes the input flow based on one column. This component can be used as intermediate step in a data flow.

This component is reverse of tNormalize processing component. In tNormalize it split records to multiple records .Here in tDenormalize it combine multiple records to single records.

In this example,  we will use tFileInputDelimited, tDenormalize, tLogRow from the Palette to the design work-space.

We are using player data and every player plays multiple games and all the games for that player is stored in Games column.

Id	FirstName	LastName	Games
1	Ryan	Arjun	Baseball
1	Ryan	Arjun	Table Tennis
1	Ryan	Arjun	Cricket
1	Ryan	Arjun	Tennis
2	Kimmy	Wang	Cricket
2	Kimmy	Wang	Tennis
2	Kimmy	Wang	Basketball
2	Kimmy	Wang	Hockey
3	Rosy	Gray	Badminton
3	Rosy	Gray	Volleyball
3	Rosy	Gray	Soccer
4	Will	Smith	Tennis
4	Will	Smith	Basketball
4	Will	Smith	Volleyball
4	Will	Smith	Soccer
5	John	Godson	Swimming
5	John	Godson	Volleyball
5	John	Godson	Soccer

Process flow -

1. On the tFileInputDelimited Basic settings panel, set the filepath to the file to be denormalized and define the Row and Field separators, the Header and other information if required.
2. In the tDenormalize component Basic settings, select the columns that contain the repetition. These are the column which are meant to occur multiple times in the document where add as many line to the table as you need using the plus button. Then select the relevant columns in the drop-down list. Select the Merge same value check box to remove the duplicate occurrences.
3. tLogRow component to show the data on the console.

We want to combine multiple rows from games column to one comma-separated value for each player.

Id	FirstName	LastName	Games
1	Ryan	Arjun	Baseball, Table Tennis, Cricket, Tennis
2	Kimmy	Wang	Cricket, Tennis, Basketball, Hockey
3	Rosy	Gray	Badminton, Volleyball, Soccer
4	Will	Smith	Tennis, Basketball, Volleyball, Soccer
5	John	Godson	Swimming, Volleyball, Soccer

To Learn more, please visit our YouTube channel at - 

http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -

https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -

https://twitter.com/macxima

YouTube demo example-