 Hello, welcome to SSUnitech social decide and today we are going to see how we can merge the multiple rows into the single row. So what does it mean? So this is one of the scenario and it is going to ask for the interviews as well. So let's assume we are having a dataset and on that dataset we will be going to have a column let me call that column as color. So under this color we will be going to have some color values and here we have something like the item ID. So under the item ID we will be having like one, one, one, two, three like that and under the color we have black, blue, all these. So we don't want to keep this information like as it is. We want to send a report to the top management and under that report we need to display the items in a single row and this should be the distinct items and the colors will be going to have is another row and all the colors will be going to add in a single row with the comma separated values. Let me quickly go into the Excel so it will be more easier to understand. So as here you can see we have the item ID, variant ID, then the item name and the variant name. So this is the input we have and in the output we need to send this report to the top management and this we want to achieve by using the azio data factory directly. So whenever we will be going to schedule or pipeline is this data will be going to process and our label with the output location where we want to keep this. So as we can see the item ID one is the three times here and the variant name which is the black, red and silver. So that's why the variant name is having three values like black, red and silver with the comma separated as you could see. Similarly for the ID two we have the three rows so we can see three as well. Here we can see for item ID three it is having two rows so we can see black and red and for the item ID four we have only a single variant so we can see black. So how we can convert this? So this is very important concept. Let me quickly go into the azio data factory will implement this in practical. So before that go to the azio blob storage here we have the file under the input location of the blob storage and the file name which is the item details dot CSV and it has the same data as we have seen over the Excel. Let me go into the azio data factory and try to add a new data flow for implementing this. So this data flow is the data flow to that is okay. Here we can see the add source. So let me click on this add source here it is asking the data set. So we have already created the data set for the same. So let me go into select that data set which is the item source. Let me open so we can verify. So it is pointing to the input location of the item details dot CSV file. So the same file. So this is we have done let me go into the data preview and try to verify the data by pressing the refresh here. So it is fetching the data so we can see all the data. So that is okay. Next as I told you we need to do the something group by on the item ID and item name and then something we need to do with the variant in the aggregation side. So as this is something group by with the aggregate so we are required to use the aggregate transformation here. So let me quickly add the aggregate transformation and under the group by as I told you we have two columns the item ID the first column and second for the item name. So we can select item ID let me click on the plus symbol so we can also add the second column which is the item name. So it will be going to grouping on these two columns and in the aggregate what we need to do we are going to replace the existing variant name value here we need to write some expression and under that expression instead of the multiple rows we want to keep this as the array values. So for doing that we need to use the collect function and under the collect function we are also required the another parameter which will be going to keep the information of your comma separated values as we want so we need to specify the comma over there. So let me go open the expression builder here let me use the collect function as I told you so under the expression we can specify the variant name here. So let me click on save and face first and go to the data preview and try to refresh so what we will see it will be going to have the values on the variant name column as an array so that we can see. So we have three values like this and we are going to see all these. So we have done till now next what we need to do we need to convert this array value into the string values so how we can do that so for conversion of this we are required to use the derived column transformation so under the derived column transformation simply we can go here under the variant name and under the expression we need to convert this value as an string so we can see two string and the value it is the first parameter so let me try to remove all these under this and here we can see this variant name let me click save and finish and go to the data preview and try to refresh so here we will be seeing all these so we need to wait until this will not be loaded as we can see the values are in array but here we are having like the double quote and the starting bracket ending bracket so we don't want to keep all these this should be having black comma red comma silver in the first row so how we can replace all these so go to the column setting again go to the expression builder so first we need to replace so let me try to use the replace function and first I am going to replace the double quote with the blank so it is going to replace double quote value next we need to replace the starting bracket so replace put comma then opening bracket put comma like this now we have replaced the opening bracket so similarly we need to also replace the closing bracket of this so let me try to use the closing bracket and replace this with the blank like this now let me try to save and finish and go to the data preview and try to refresh so we will be going to see the data here so we need to wait and here we can see the data as it was expected in the Excel file so it has item ID item name and the comma separated values on the variant name that we can see here so if we don't want comma separated value we want pipeline separated value then how we can do that simply we can go here and here we can replace this comma by pipeline so it will be like replace your comma value by pipeline like this and now we can save and finish let me go into the data preview try to refresh so we will be going to see it will be going to have the pipeline separated instead of comma as we can see here now let me put this into the sync location so we can simply add a sync location here so under the data set let me try to use the inline query and we want to generate a delimited text file and then we need to select the link service so SSU testing the link service we have already created in the earlier videos go to the setting and here we need to select the path by which we want to keep the file so we want to keep the file into the output location so we can select that and in the below side we can see first row as header so that is true then we can see file name option so we want to keep output a single file and the name of the file will be item info dot csv file now go to the optimize and use the single partition instead of use current partition let me try to save this and let me add a new pipeline for executing this Dataflow 2 that we have created here so we can drag and drop this Dataflow 2 here and try to execute so once it will be executed we will be having another file in the output location with the item info dot csv let me go into the output location and we will see there so here as we can see file is in progress so let me try to refresh so here we can see the item info dot csv file is here and we need to check the data so we can go into the edit mode and here preview the data so we can see all the items and after that we can see the values here like that so this is what we have done in this video we have converted the multiple rows values of any column to the single row of that column only so I hope guys you have understand how we can do that so thank you so much for watching this video see you in the next video.