Why Do People Think Learn Step by Step PIVOT Transformation is a Good Idea? TIP #125

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 a few easy steps.

Let’s understand the PIVOT by the below example. Suppose you have salary data of employee as shown below:

Now, if our requirement is something like the below table, then we would require PIVOT functionality.

In general, we say when we want to convert Rows data to Columns, then this is called Pivoting.

Now, let's create a sample of PIVOT transformation with a few basic steps. In this example, we are again using AdventureWorks database. EmployeePayHistory, Employee, People tables are the base of our example. We wrote a query which fetches data similar to what is shown above.

Let’s move step by step.

Step 1

Add a dtsx package and drag drop data flow task as shown in the 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 the above image, it is the year wise columns. If you are sure about 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 the 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 exists in Input columns, then you have to select Pivot Default Output and click on add column. Now, the main important point is 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 the below images).

Step 6

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 configured, 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 the below figure. I hope you will not face any issue in this. If you are new and facing any difficulties, then please visit the previous articles of Zero to Hero in SSIS Series.

Step 8

Once the configuration is done, we will see the below screen.

Step 9

Now, once everything is completed, then we run the package and we will get the following screen.

Now to cross check, we open Excel and cross check the data. When we open Excel, we found the desired result.

So, we have achieved what we wanted. We did our first Pivot Transformation example.

Please feel to leave your comment.

Enjoy !!!

Filed under: CodeProject, Database, Sql server, SQL Server Integration Service, SSIS, TIPS Tagged: pivot, PIVOT transformation, SSIS training, step by step SSIS