http://www.sqlservercentral.com/blogs/dknight/2009/11/07/better-know-a-ssis-transform-_1320_-the-pivot-transform/

Printed 2014/09/19 06:41PM

Better Know A SSIS Transform – The Pivot Transform

2009/11/07

 

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-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.