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 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.

4

Let’s move step by step

Step 1: Add a dtsx package and drag drop data flow task as show in below figure.

1

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.

2

3

Step 3:- Now, We have to drag drop PIVOT transformation control as shown below.

5

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.

 

15

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)

7

8

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.

9

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 Smile.

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

10

11

Step 8 :- Once the configuration is done we will see below screen.

12

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

13

Now to cross check we open the excel and cross check the data. When we open the excel, we found desire result.

14 

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

Please, feel to drop your comment.

Enjoy !!!

RJ!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s