http://www.sqlservercentral.com/blogs/dknight/2009/06/15/better-know-a-ssis-transform-the-pivot-transform/

Printed 2014/11/28 05:40PM

Better Know A SSIS Transform – The Pivot Transform

2009/06/15

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.  Screenshots are included at my regular blog site http://blogs.pragmaticworks.com/devin_knight/

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

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.