Pivot and UnPivot Transformation

-

Working with Pivot and UnPivot Transformation

Table of Content
  1. What is Pivoting?
  2. A Pivot Example 
  3. What is UnPivoting?
  4. An UnPivot Example
  5. Conclusion

What is Pivoting?

Pivoting is a mechanism where we interchange the rows into columns. In SSIS, we have the Pivot Transformation component that does the same task. In this short article, we will explore this transformation with a step by step approach.

A Pivot Example

Given a flat file as data source whose structure looks as under
Month  DayOfWeek Expenses
January  Sunday  200
January  Monday  100
January  Tuesday  123
January  Wednesday 154
.......................................
.......................................

April   Saturday 344
The output for this record set should be as under
1.jpg

Steps to accomplish the work

Step 1: Open Bids.Choose Integration Services Project from the available project type.Drag and drop a Dataflow Task in the control flow designer
2.jpg
Step 2: Drag a flat file source in the Data flow. In the connection manager, specify the data source and check theColumn names in the first data row check box
3.jpg
Ensure that the Column delimiter in the Columns tab is set to Tab {t}. Click OK.
Step 3: Drag and drop a Pivot transformation into the data flow area.
4.jpg
Step 4: Specify the data flow from Flat file Source to the Pivot component and then double click on the Pivot component. The Advance Editor for Pivot opens
5.jpg
Step 5: Click on the Input columns tab and select all the columns
6.jpg
Step 6: Next we have to go to the Input and Output properties tab and expand the Pivot Default Input tree.
7.jpg
Set the Pivot Usage of Month column to 1, DayOfWeek to 2 and Expenses to 3.
Next in the Pivot Default Output tree, we have to create 8 columns namely Month,Sunday,Monday,Tuesday,Wednesday,Thrusday,Friday,Sarurday.
For the Month column, set the Source column to the Lineage ID of the Month Column of the Input column
8.jpg
Next for the other Output columns, the Source column should match with the Lineage ID of the Expenses. Henceforth, the Output columns now look like
9.jpg
On the Sunday Output column, set the PivotKeyValue to Sunday
10.jpg
Similarly for Monday it will be Monday, Tuesday it will be Tuesday and the like. Once done with all these setting, we need to click OK button.
Step 7: Add a Row Sampling and enable the data viewer. The final package design looks as under
11.jpg
Run the application and we will get the needed output.Hope this small experiment has helped us in understanding how to work with Pivot Transformation. We can go ahead and do more complex transformations with this powerful component. In the next section we will look into the other part of the coin i.e. UnPivot

What is UnPivoting?

If pivoting means rows to column transformation, then unpivoting is the opposite of that. In SSIS, we have the UnPivot Transformation component that does the same task. In this short article, we will explore this transformation with a step by step approach.

An UnPivot Example

Given a flat file as data source whose structure looks as under
Month Sunday  Monday  Tuesday  Wednesday Thrusday  Friday  Saturday
January   200 100    123  154 50   110    600
February  400 200    523         754 450   1810     6800
March   2900 1900      1923  1954 590   1910     6900
April   800 10    12  15  5   11    60
The desired output should be
12.jpg

Steps to accomplish the work

Follow the steps 1 and 2 from the Pivot Transformation example
Step 3: Drag and drop a Pivot transformation into the data flow area.
13.jpg
Specify the data flow from Flat file Source to the UnPivot component and then double click on the UnPivot component. The UnPivot Transformation Editor opens
14.jpg
Step 4: In the Input Column Section add all the Week day names (i.e. Sunday, Monday etc.)(Numbered as 1 in the below figure). In the Destination Column Section, type Expense.(Numbered as 2 in the below figure).
N.B.~ The Pivot Key Value column will have the same value as the Input column.
In the Pivot key value column name, specify the Pivot key value as Weekdays (Numbered as 3 in the below figure).
15.jpg
Once the settings are done we must click on OK button.
Step 5: Add a row Sampling and Add data viewer to the data flow paths. The final package design looks as under
16.jpg
Run the application and we will get the needed output.Hope this small experiment has helped us in understanding how to work with UnPivot Transformation. We can go ahead and do more complex transformations with this powerful component.

Conclusion

Pivot and UnPivot are very useful and indespensible transformation.Hope this article has helped in understanding the same.Also we have learnt how to configure those components, the importance of Lineage ID in Pivot transformation etc. 

Comments

Post a Comment

Popular posts from this blog

SharePoint 2007 - Simple Task Dashboard

-
Image
example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
Read more

MERGE transformation in SSIS

-
Image
In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
Read more