posted 11/7/2009 by DevinKnight - Views: [6235]
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
Excellent example Devin, Very well explained.
Thanks!!
Pradeep
Wow great post Devin. For whatever reason I've had trouble wrapping my head around pivots and wasn't sure how this particular transform worked, nicely done.