 Hey guys welcome to SSUnitag social decide and today we are going to see about the pivot transformation in data flows. So what is pivot transformation? So to use the pivot transformation to create multiple columns from a unique row values of a single column. The pivot is an aggregation transformation where you can group by columns and generate the pivot column using the aggregate function. So basically we can say we will be going to have one input table and where we will be going to have one of the column. So it's just a rotation of this column distinct values from this column and we will be going to have the columns there. So for example here let's assume we are having employee table and it has something like the department. So it has the IT department then we have HR department then we have payroll department. So by using the pivot transformation we can rotate this like to the different columns. So first we will be going to have the IT department then it will have the HR department and then it will have the payroll department. So all these will be having the different columns and we can also use the aggregate values for any other column. So go to the source and we will try to see there. So here our source is one of the input table that we can see. So this CSV file under these blog stories. So here it is having the data for the expenses of the employees. So like here we can see the employee ID one so one ID for the month of Jan it will be going to have the day and then it has the expenses. So for example like employee ID one is coming four or fifth time. So what we want to get from this so we just want to pick this day of week column from here and we'll get the distinct values from this. So that will be the day like Monday Tuesday Wednesday Thursday Friday and we'll be rotating this from this order to like the output of this will be employee ID then the month then day of week will not be here it will be having Monday Tuesday Wednesday Thursday Friday and the expenses will be going to some on the basis of day of month and we are doing the group on the employee ID and month. So let's try to implement this in practical and after doing this rotation we want to keep this in the output folder of the blog stories. So here let me try to add a new data flow and let me call this data flow as pivot 0.1 something like this. Now here we need to add the source so we can add the source and let me go here let me use the new data set here. So let me try to click on new and this is as your blog stories we can click on continue then it will have the delimited text so we can select and click on continue. Then we can call this data set as Pivot source. We can select the link service name so that is SSU testing first row as header and we can browse and try to select the pivot file. So that file is available under the input folder and under this we can see the pivot.csv we can select and click on continue then we can click on OK. So this data set will be created and it is pointing to the pivot file. We can go in the data preview and try to click on this refresh so we can see the data now we can see the data here let me click on this plus symbol to add the pivot transformation. So here we can see this pivot transformation so click on that and after that here we can see the output stream name so that will be the pivot one we can call this like pivot so that is OK then the incoming stream so incoming stream is the source one that is also OK. Here we can see the group by so we need to do the group by on the basis of employee ID and month so we need to add these two columns there under the group by here we can see the columns so first we can select the employee ID then we can see the plus symbol here so let me add the another column and this column is the month so we have selected these two so this is the group by now we can go in the pivot key so under the pivot key here we need to specify the column which we want to do the rotate so we just wanna do the rotation on the day of week so we can select that and after that here we can see the inter values and this is the optional so it will be going to generate automatically the distinct values from this column so no need to specify anything here now we can go in the pivoted column so under the pivoted column here we can see the pivot expression so what will be the expression so we just want to do the sum of this expenses so we can go so here we can do the sum of numeric value that will be the expenses so this is for that and what will be the column name we can specify here so the column name may be expenses like that now so it is saying it is expecting the number and this type is not correct so let me try to cancel this and go to the source and here go to the projection and under the projection we can see the expenses so let me try to make this as integer now go back to the pivot again and go to the expression and under this expression we can have the sum of the numeric value from the expenses so this is good now now we can click on save and finish so everything is done here we can go directly in the data preview and try to refresh it here so it will be going to rotate as we have discussed so here as you can see it is having like expense Friday expression Monday expense Thursday so this is something like so go back to the pivot setting again and here we can see this column so if you are not going to specify anything here because this is optional now go back to the data preview and try to refresh it again so that text will not be appended there so that we can see here we can see the day of week so like Friday Monday Thursday Tuesday Wednesday and all these so this is we have done now we need to put this in one of the sink so we can add the sink here and under this sink we can go this time by using the inline and under this we can see the inline dataset type so that should be the delimited text we can select it now we can see the link service so we can select the link service here and after that we can go in the settings and here we need to specify the folder path by which we want to keep the file so that will be in the output folder we can select and click on ok now here we can see the first row as header we can select that and after that we can go here the file name option so let me output a single file and let me call this file name as pivot data now we can go in the optimize and this time we need to use the single partition as we have already seen in the earlier videos now we can publish this so once we will be publishing this so it will be published the data set that we have created and this data flow so public is in progress in between let me try to create the pipeline and public is completed so let me use the data flow here so data flow activity let me try to go in the setting directly and let me select the data flow that we have created so that is for the something pivot 0.1 now we can debug it so once we will debug it so it will be going to generate one of the file in the output location so let me go here so go to the output folder and under the output folder here we should see the pivot data file so this file will have the data so let me open this go to the edit and it should have the data as we have seen so that we can see like employee ID then the month then the day of week like Friday Monday Thursday Tuesday and Wednesday and whatever the summarized value on that so that we have seen here so this is all about the pivot transformation if you have still any doubt then you can comment your question in the comment box thank you so much for watching see you in the next video with unpivot transformation