Can anyone help me in providing solution for the below scenario.
We have source data like this.
Now, we want output like below.
|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.
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.