In the series of step by step SSIS learning, this is another interesting article. In this article we will try to implement PIVOT transformation in few easy steps.
Let’s understand the PIVOT by below example. Suppose, you have a salary data of employee as shown below.
Employee Name | Salary($) | Year |
Sandeep | 110000 | 2016 |
Sunil | 120000 | 2016 |
Shreya | 130000 | 2015 |
Virendra | 140000 | 2015 |
Sandeep | 120000 | 2014 |
Sunil | 130000 | 2014 |
Now, if our requirement is something like below table then we would require PIVOT functionality.
Employee Name | 2016 | 2015 | 2014 |
Sandeep | 110000 | 0 | 120000 |
Sunil | 120000 | 0 | 130000 |
Shreya | 0 | 130000 | 0 |
Virendra | 0 | 140000 | 0 |
In general, we say when we want to convert Rows data to Columns then this is called Pivoting.
Now, lets create a sample of PIVOT transformation with few basic steps. In this example, we are again using AdventureWorks database. EmployeePayHistory, Employee, People tables are base of our example. We wrote query which fetch data similar as shown above.
Let’s move step by step
Step 1: Add a dtsx package and drag drop data flow task as show in below figure.
Step 2:- Now, double click on Data flow task and drag drop data source and select SQL Data source and configure the SQL statement as shown below.
Step 3:- Now, We have to drag drop PIVOT transformation control as shown below.
Step 4:- Now, we have to configure the PIVOT control. When you choose edit option then you will get the following screen.
Here, we have to choose Pivot Key, Pivot Value and Set Key. In the Pivot Value we use the value which we have to show in the columns.
If you see pivoted output columns highlighted in yellow in above image is the year wise columns. if you are sure for the columns which you want as a pivot columns then you can add the name of the columns in place of [value1],[value2] etc. and click on Generate Columns Now button. (In current case we have limited years so we have added [1997] to [2004] values in this textbox and clicked on Generated Columns Now button due to which we got yellow required Pivot output columns as highlighted yellow.
Step 5:- Our configuration is not yet complete to configure this we have to click on advance setting and select Input and output properties.
Here, if you want to add any extra column as an output column which exist in Input columns then you have to select Pivot Default Output and click on add column. Now, the main important point if we want to map it with existing input source column then in such case we have to add LineageId of that particular column.(AS highlighted in below images)
Step 6:- The another most important point is PivotUsage which is highlighted below. If we want to add columns which are not affecting the pivot then we have to update the value of PivotUsage to 0.
Step 7:- Once the Pivot is configure the next step is to provide the output of pivot to a destination. As a destination we are using Excel file as usual .
So, drag drop excel destination source and configure it as shown in below figure .I hope you will not face any issue in this. If you are new and facing any difficulties then please visit previous articles of Zero to Hero in SSIS Series
Step 8 :- Once the configuration is done we will see below screen.
Step 9:- Now, once everything is completed then we run the package and we will get following screen.
Now to cross check we open the excel and cross check the data. When we open the excel, we found desire result.
So, We have achieved what we want. We did our first Pivot Transformation example.
Please, feel to drop your comment.
Enjoy !!!
RJ!!!