The Pivot Transform – Now with UI!

Published Mar 25 2019 03:26 PM 190 Views
Not applicable
First published on MSDN on Nov 17, 2011

One of the post-CTP3 changes for SSIS in SQL Server 2012 is the addition of a user interface for the Pivot transform. This post walks through the new UI, which can be found in the new RC0 preview release.

Sample Data

For this example, we’ll be pulling data from the AdventureWorks sample database. We’ll be grabbing the total sales of all products in the Accessories category (ProductCategoryID = 4), grouped by year.

  1. SELECT YEAR(d.DueDate) as [Year], p.Name as [Product Name], SUM(d.LineTotal) as [Total]
  2. FROM Purchasing.PurchaseOrderDetail d
  3. INNER JOIN Production.Product p ON p.ProductID = d.ProductID
  4. INNER JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
  5. WHERE s.ProductCategoryID = 4
  6. GROUP BY YEAR(DueDate), p.Name

The data will look something like this

Year Product Name Total
2004 HL Mountain Tire 1504884.15
2003 Road Tire Tube 35920.50
2004 Water Bottle - 30 oz. 2805.00
2002 Touring Tire 62364.225

We want the end results to be pivoted to look like this (Total product sales by year):

2002 2003 2004
HL Mountain Tire 141164.10 446297.775 1504884.15
Road Tire Tube 3592.05 35920.50 89801.25
Water Bottle - 30 oz. NULL NULL 2805.00
Touring Tire 62364.225 375051.60 1041810.00

Setting up the Source

Add an OLE DB Source transform, and add a connection manager for the AdventureWorks sample database. Paste in the query to retrieve the total product sales by year:

Add a Pivot Transform from the SSIS Toolbox (found under the Other Transforms section by default).

Connect the Source to the Pivot transform. Double click the Pivot transform to open its editor.

The Pivot UI shows a sample pivot table at the top of the form, where you configure the Pivot Key (the column to use for values across the “top” of the table), the Set Key (the column to use for values down the “left” of the table), and the Pivot Value (the column to use for the values in the middle). The bottom of the UI is where you configure the pivot key values.

After mapping my columns to the appropriate keys, the UI looks like this:

  • Pivot Key –> Year
  • Set Key –> Product Name
  • Pivot Value –> Total

The Pivot transform requires you to enter all of the possible Pivot Key values (so it can create output columns for each one). You can manually enter each key here (in our case, we’d have a value for each year that appears in our data set – 2002, 2003, and 2004). Alternatively, we can click the “Ignore un-matched Pivot Key values” checkbox and run the package as is. The pivot transform will output a log message containing all of the key values that we can then copy and paste into the UI.

  1. Check the “Ignore un-matched Pivot Key values and report them after DataFlow” execution box
  2. Click OK to save the changes to the UI
  3. Run the package in the designer
  4. When the package succeeds, click on the Progress tab
  5. Look for an information log message from the Pivot transform which contains the keys
  6. Right click the message and select Copy Message Text
  7. Click Stop to end the execution
  8. Double click the Pivot transform
  9. Uncheck the Ignore un-matched Pivot Key values checkbox
  10. Paste the Pivot Key values into the bottom text box
  11. Trim the text so that it only contains the key values – “[2002],[2003],[2004]”
  12. Click the Generate Columns Now button

The Pivot UI should now look like this:

Click OK to save the changes. We’ll add a Row Count transform to the data flow, and connect it to the Pivot’s output. Add a data viewer on the path so we can see the end results.

Run the package, and we can see the pivoted results.

Version history
Last update:
‎Mar 25 2019 03:26 PM
Updated by: