Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

How to flatten data using the Pivot Transform in SSIS.

  • 30 November 2012
  • Author: MitchellPearson
  • Number of views: 9629
  • 0 Comments

The Pivot Transform is a very powerful tool but often overlooked and isn't the first thing to come to mind when you need it most. Imagine you have a table that has an employee listing for each type of phone number. For example, you have one row for business phone, personal phone, and cell phone. We want to clean this data up and put all these phone numbers in one row on with only one listing per employee. Well this is the situation I was presented with recently. The great news is with the Pivot Transform in SSIS this can be accomplished fairly easy. The table below is an simplified example of the data we had to work with.

EmployeeID PhoneType PhoneNumber
1 Business 123-987-6543
1 Personal 231-789-3456
1 Cell 312-897-5643
2 Business 321-978-6534
2 Personal 132-798-5634
2 Cell 213-879-3564

Our end product will look like this:

EmployeeID BusinessPhone PersonalPhone CellPhone
1 123-987-6543 231-789-3456 312-897-5643
2 321-978-6534 132-798-5634 213-879-3564

Step 1) Set up a data flow task in the control flow.

Step 2) Open up the data flow and pull in your source component. For this example I created a table in Sql Server.

Step 3) Drag in a Pivot Transform and link it to the source component.

Step 4) Configure the Pivot Transform

I was going to go through step by step instructions on how to configure the Pivot Transform. However, there is already an awesome blog out there by Devin Knight. Please click on the link below to view his blog post.

STEP 3 in Devin's post explains how to configure the Pivot Transform.

http://bidn.com/blogs/DevinKnight/ssis/85/better-know-a-ssis-transform-%E2%80%93-the-pivot-transform

Thanks for Looking!

Print
Tags:
Rate this article:
No rating

MitchellPearsonMitchellPearson

Other posts by MitchellPearson

Please login or register to post comments.