Home > SSIS > Pivot and UnPivot Transformations in SSIS

Pivot and UnPivot Transformations in SSIS

Pivot and UnPivot Transformations are available in SSIS Data Flow transformations.

We have Pivot and UnPivot functionality in SQL Server database as well. The same functionality we can get from the SSIS designer too using above said transformations.

Let see the configurations and functionality of both transformations in one package.

Lets prepare some data as shown below:-

Student Name Subject Marks
Prasad Maths 80
Prasad Physics 75
Prasad Chemistry 85
Prasad Biology 77
Dilip Maths 75
Dilip Physics 85
Dilip Chemistry 77
Dilip Biology 80
Karthik Maths 90
Karthik Physics 80
Karthik Chemistry 85
Karthik Biology 78

Above Data should be Pivot to as shown below:-

Student Name Maths Physics Chemistry Biology
Prasad 80 75 85 77
Dilip 75 85 77 80
Karthik 90 80 85 78

after Pivot will do unpivot and get the same data as before doing Pivot.

Lets see in SSIS, how to do it.

Prepare data as shown in above example:-

SELECT  ‘Prasad’ AS StudentName ,‘Maths’ AS Subject,80 Marks

UNION

SELECT  ‘Prasad’,‘Physics’,75

UNION

SELECT  ‘Prasad’,‘Chemistry’,85

UNION

SELECT  ‘Prasad’,‘Biology’,77

UNION

SELECT ‘Dilip’,‘Maths’,75

UNION

SELECT ‘Dilip’,‘Physics’,85

UNION

SELECT ‘Dilip’,‘Chemistry’,77

UNION

SELECT ‘Dilip’,‘Biology’,80

UNION

SELECT ‘Karthik’,‘Maths’,90

UNION

SELECT ‘Karthik’,‘Physics’,80

UNION

SELECT ‘Karthik’,‘Chemistry’,85

UNION

SELECT ‘Karthik’,‘Biology’,78

Open the SSIS solution in BIDS. Create new package and add Data Flow Task (DFT) into the designer.
Double click on DFT and go inside of the DFT , add Ole DB Source component.

In OLE DB Source component copy paste the above code which will become our source data which needs to be pivoted.
Above step is shown in below screen shot.

Source Query

After this drag Pivot component from toolbox to designer. usually its in left side of the designer.

Pivot Component

Open the Pivot Editor by double click , go to the Input Columns and set the Avilable columns as shown below screenshot:-

PivotEditor

After above screen go to the Input and Output Properties screen and expand input Columns, it will look like below screen:-
Input Output Properties

After clicking on the Input column individually , we can see the properties of each column.
We have to set the PivotUsage property for each column as shown in below screen:-

Pivot Usage Property

StudentName Column should have 1

Subject Column should have 2

Marks Column should have 3

You may wonder what is these values 1,2,3 signify.

1 = column values become the rows of the pivot .

2= column values become the column names of the pivot , Different Subjects as value will be column name

3= column values that are pivoted in the pivot , Above Columns will have value of this column.

One more value is there 0 (Zero) which is used when we dont want that column to take part in pivot (pass through).

After input column properties are set, next step to set the Pivot Default output properties.

before Output column properties, we need to add columns as shown in below screen:-

PivotOutputAddColumn

Lets create column First Column StudentName and set the two properites, If these properties are not correct it will not alow to save also.
Property:- PivotKeyValue and SourceColumn as shown in below screen.

OutputColumnProperty

PivotKeyValue as Column Name which has value.

SourceColumn is LineageID of the Input Column which is 17 here, Lets verify by seeing LineageID of input Column which is shown in below screen:-

LineageID InputColumn

We need to notedown of all input columns Lineage ID which we  using in Pivot Transformation.

Next Column is like pivoted Column as shown in below screenshot:-

PivotedOutputColumnProperty

PivotKeyValue as the value.

SourceColumn which has the value to be shown under this pivoted column, so this Marks to be shown under Maths subject.

So Marks column LineageID should be entered, in above case its should be 23.

Like that rest columns to be created.

Add one Unionall component and then add precidence constraint and add dataviewr control to see the data.

Execute the package and it will show data as shown below screen:-

PivotResult

Lets remove the UnionAll and place Unpivot component as our data got pivoted we can use it as source and lets unpivot it using Unpivot transformation.
After this again we can get the actual data which is we used in source.

Double Click on unpivot transformation  it will open the editor as shown below:-
Unpivoteditor

In Student Column we dont want any change so we checked the Pass Through Checkbox.

Pivoted Column only we taken and give the Destincation column Subject, So that all values comes in Subejct column.

Pivot Key value column name is the column name which value will be come in front of Subjects in Marks Column.

Now we are done with Unpivot.

lets add dataviwer control to see the data.

PivotUnpivotResult

We can see Source data then Pivoted data then lastly Unpivoted data in above screen.

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSIS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: