Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server / SQL Server 2008

Working with Pivot and UnPivot Transformation

Rate me:
Please Sign up or sign in to vote.
4.83/5 (13 votes)
9 May 2011CPOL4 min read 91.1K   2.1K   19   6
This article will describe in simple ways as how to work with Pivot and Unpivot transformation component

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 the Column 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. Thanks for reading the article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
QuestionC# Pin
Member 1105672811-Dec-14 8:30
Member 1105672811-Dec-14 8:30 
GeneralMy vote of 5 Pin
HikmetT Ttuncer23-Oct-14 2:27
HikmetT Ttuncer23-Oct-14 2:27 
Questiongood material Pin
Member 107320117-Apr-14 18:36
Member 107320117-Apr-14 18:36 
QuestionHi Niladri Pin
prudentpeeps26-Mar-14 2:33
prudentpeeps26-Mar-14 2:33 
Generalnice article Pin
surjit.lakhnotra27-Feb-13 22:36
surjit.lakhnotra27-Feb-13 22:36 
GeneralGood one Pin
Khan Irfan10-May-11 0:22
Khan Irfan10-May-11 0:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.