Printed 2017/08/21 01:53PM

Better Know A SSIS Transform – The Pivot Transform



This is part 1 of my 29 part series called Better Know A SSIS Transform.  Hopefully you will find the series informative.  I will tell you a little about each transform and follow it up with a demo you can do on your own.

Pivoting is a common business practice to gain a better visualization of company performance.  Basically the purpose of pivoting is to changing rows into columns.  So if you want to display sales across all months you would use pivoting to turn a single date column with the month into 12 columns with all the months listed.  You can accomplish this in TSQL or using the Pivot Transform in SSIS.

When you first try using the Pivot Transform you may be a little intimidated.  This transform is not as easy to configure as many of the other Data Flow Transforms.  It sends you straight to an Advanced Editor and you can't just click a couple boxes to complete it's configuration. 

My goal is to hopefully demystify using the Pivot Transform so those who have a real need to pivot data can accomplish that inside a SSIS package.  For this example I will be using the AdventureWorksDW2008 database.

Example Overview




Step 1: Configure Source




SELECT p.EnglishProductName AS ProductName,

SUM(f.OrderQuantity) AS OrderQuantity,

d.EnglishDayNameOfWeek AS DayofWeek

FROM FactInternetSales f INNER JOIN

DimProduct p ON f.ProductKey = f.ProductKey INNER JOIN

DimDate d ON f.OrderDateKey = d.DateKey

GROUP BY p.EnglishProductName, d.EnglishDayNameOfWeek, d.DayNumberOfWeek

ORDER BY p.EnglishProductName, d.DayNumberOfWeek

Step 2: Add Data Viewer




Step 3: Add Input Columns


Step 4: Configure Input Columns


  Expand the Pivot Default Input and Input Columns

Step 5: Configure Output Columns


Step 6: Add Data Viewer and Destination


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.