Solution required for one ETL scenario in BODS

[former_member208402]

Hi

Can anyone help me in providing solution for the below scenario.

We have source data like this.

Country	City
India	Hyderabad
India	Bangalore
India	Mumbai
USA	Sanjose
USA	New york
USA	Boston
USA	Los angeles

Now, we want output like below.

Country	City
India	Hyderabad,Bangalore,Mumbai
USA	Sanose,New york,Boston,Los angeles

Can any one help us for this scenario with detailed steps/logic we need to implemented to achieve the above output.

Thanks,

Ravi kiran

[former_member187605 · Accepted solution]

If you know the maximum number of cities in advance, use a Reverse_Pivot transform. Then concatenate all pivot columns into 1.

[former_member241220 · Accepted solution]

Hi Ravi Kiran,

If you found the solution regarding this requirement. please can you post here.It will help to me and another team members.

Thanks and Regards,

Murali

[former_member208402 · Accepted solution]

Hi Murali,

This was the question asked in an interview.

I didn't work on this scenario and the solution suggested by Dirk would work for this scenario.

Thanks,

Ravi kiran.

[former_member241220 · Accepted solution]

Hi Ravi Kiran,

Even I didn't work on this scenario, but I have one requirement seems to be same like this. If you want I will share.

Thanks and Regards,

Murali.

[Former Member]

Unfortunately, Reverse pivot will only work if you know the number of columns ahead of time like Dirk Venken mentioned. However, if the number is unknown and you would like to still do something like this, you can use a SQL transform with a query that groups by country and concatenates the list of city strings into a comma delimited list.

For MySQL I think there is a group concat function built in, but for SQL Server you may need something like this: How to use GROUP BY to concatenate strings in SQL Server? - Stack Overflow.

This will allow you to get a comma delimited list (just add the city as another piece of the select) and map it into your dataflow like you normally would!

We use something like this in one of our batch jobs and when joined to a driver table to cut down on rows (say 4000-10000 rows per batch) it runs quite fast - less then half a second. for a 4m row database table it runs in about 20 seconds.