Working with Pivot and UnPivot Transformation
Table of Content
- What is Pivoting?
- A Pivot Example
- What is UnPivoting?
- An UnPivot Example
- Conclusion
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.
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
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
Step 2: Drag a flat file source in the Data flow. In the connection manager, specify the data source and check the Column names in the first data row check box
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.
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
Step 5: Click on the Input columns tab and select all the columns
Step 6: Next we have to go to the Input and Output properties tab and expand the Pivot Default Input tree.
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
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
On the Sunday Output column, set the PivotKeyValue to Sunday
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
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
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.
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
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.
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
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).
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
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.
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. Thanks for reading the article
