 Hello friends, welcome to SSUnitech. So, see this side and this is continuation of SSIS tutorial. So, today we gonna discuss about Pivot Transformation. So, let's start with Pivot Transformation. So, it is used to interchange rows into columns. Or we can say that the Pivot Transformation makes a normalized dataset into a less normalized but more compact version by pivoting the input data on a column value. Next is what is the prerequirement for using Pivot Transformation. So, first is short rows in input data. So, input data should be sorted with the pivot key. If data is not sorted, the Pivot Transformation might generate multiple records for each values in the set key. Next is duplicate rows. So, input data might contain duplicate rows which will cause the pivot transformation to fail. So, our input data should not contain the duplicate rows. So, these are the pivot steps. So, before going forward, let's have a look of our source. So, this is our flat file source as we can open. Then we can see we are getting employee ID month, day of week and expenses. So, after using Pivot Transformation, our output should be like this. So, this is our input and after using Pivot Transformation, our input looks like this output. So, first we have employee ID. Then in our columns, then last column is month. So, this would be our output after using Pivot Transformation. So, our first is pivot uses. So, it would contain 0, 1, 2 and 3. For 0, it doesn't participate that column in the pivot transformation. When we select 1, that is for set key. So, for each unique values for the column. When we select 2, then this is for pivot key. So, for each value of the column add source created new columns at the destination. And for 3, this is for pivot values. Add output columns manually. So, here we need to generate our output columns manually. Next is map the linux property add the source with source column property at the destination. And last is we need to set the pivot key value for the newly created column. So, go to on ssdt to implement the package. So, I gonna add one more package. So, I click on this SSIS package, then new SSIS package. Here I wanna rename this package with pivot. So, we can select pivot. Then we have to use dataflow task. So, we need to drag and drop this dataflow task. After that, we need to double click on this dataflow task. So, here we need to select our source. As we have discussed, we have flat file source. So, we need to drag and drop this flat file source. Then double click on this flat file source. So, here we need to select our flat file connection manager. So, click on new. Then we need to select the file. So, we can browse. And this is our input file. So, we can open. Now, go to on columns. As we can see, we are getting all the column. Click on okay. Now, go to again columns. So, we want all the columns that is fine. So, click on okay. Now, we have done our source. Now, we need to configure our transformation. And that is pivot transformation. So, I wanna drag and drop this pivot transformation. After that, we need to connect with source. Now, double click on this pivot transformation. So, here we need to take care. First is pivot key. Second is set key. Then pivot value. In our pivot key, we need to select our day of weeks, which will generate our newly created column. Then set key. Here we need to select employee ID. In pivot value, we need to select expenses. So, here as we can see, generate pivot output columns from values. So, here we need to generate columns manually. As I have already typed, so I wanna copy this and paste that value over here. Then we can see this button generate column now. You can generate columns now. Now, click on okay. So, columns has been generated successfully. We can rename these columns later. Here we can see this checkbox. So, ignore unmasked pivot key values and report them after data flow execution. So, we need to select this checkbox. Now, click on okay. So, we have done our pivot transformation. But I am not going to load data in any destination. I just need to check the data. So, we need to drag and drop this multicast. After that, we need to connect with pivot transformation. Then we can enable data viewer. Then we can save our packets and execute. Once we execute our packets, then we can see we are getting employee ID Friday, Monday, Thursday, Tuesday and Wednesday. So, we are getting all the data. But we should have one more column with the month. So, we are not getting over here. Now, we can stop this package. So, here as we can see, we don't have any option to select more than one columns in set key. So, we can close it. Then right click on this pivot transformation. Then so, advance enter. Then go to our input columns. So, here we have month. So, we can select this month. Go to all input output properties. So, we can expand. As we can see, we are getting month in our input column. Go to our output columns. So, we don't have month column. So, I am going to add one more column. And this is for month. So, go to our input columns. So, in month, we need to pick the line as ID. So, that is 14 in my case. When you will be practicing, then it could be different. So, in this source column, we need to specify 14. Then pivot key value. Here, we need to specify 0. Now, click on OK. Now, delete our multicast. And then drag and drop one more time. And now, we need to connect with pivot transformation and enable data everywhere. Now, I want to execute our package. Once we execute our package, then we can see we are getting the data. So, this is our output data. Now, we can match. So, we are getting the same data as we expected. So, we can close this and stop this package. So, here as we can see, go to our advance editor, input output properties, then output properties. So, here we can see, we can rename this column name as well by Friday. And this is for Monday. So, we can rename for Monday. And we can do the same for all the columns according to our requirement. Now, we can save our package and execute. So, we can see, we are getting Friday and Monday. So, we can close this and stop our package. So, what are the limitations for pivot transformation? So, here we need to create the column manually. Second is, it can't be summarized data. If our sequence over, while we are going to use pivot operator, then we can summarize the data. But here, we can't. Next is, data should be sorted. So, these are the limitations. So, thank you so much for watching this video. If you like this video, please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.