Sometimes coincidences happen that make you sit back and say Hmm. Couple of days back Ovidiu, one of our senior developers, came by and asked me to blog about the Pivot transform. Then earlier today someone doing a high end POC down south need the same information. Hmm.

Pivot transform converts rows into columns. One of the items we will likely not be able to attend to for SQL Server 2005 is a custom user interface for this transform. Thanks to the advanced UI this component is still configurable but that exposes the guts of the transform and perhaps more than you wanted to know. The steps are not pretty but it's better to have such information released than have folks not use features because they don't know how to set it up.

Until BOL gets more in depth information on this topic, here're the steps to configure the Pivot transform. Thanks to Wenyang, one of my colleagues for sending these over... what a team... someone suggests what to write about, someone else writes it up, and I get the limelight.

The Pivot transform converts data that is organized like this:

Table 1:

Into similar data that is organized like this:

Table 2:

Steps:

1. Assume that the data flow contains a path with the three columns and shape of data that is show in Table 1.

2. Add a Pivot transform

3. Connect the path from component in step #1 to the input of the pivot transform.

4. Bring up the advanced editor for the pivot transform.

5. Select the Input Columns tab and pick all three columns.

6. Go to the Column Properties tab and expand the input list and the input columns list.

7. For the Customer column, set the PivotUsage property to 1 (the SetKey). Write down the value of the LineageID property.

8. For the Product column, set the PivotUsage property to 2 (the PivotKey).

9. For the Qty column, set the PivotUsage property to 3 (the PivotedValue). Write down the value of the LineageID prperty.

10. Expand the Outputs list and select the OutputColumns list.

11. Click the Add Column button once for the Customer column and once for every possible value that may show up in the domain of the Product column. In this example that means add 5 more columns.

12. Set the name of the first column to Customer and set the SourceColumn property to the LineageID value from step 7.

13. For the other columns, give them reasonable names that reflect the associated product value and the data value that will be contained. In this example the names will be: HamQty, MilkQty, BeerQty, BreadQty and CheeseQty.

14. Each of these new columns should also have the SourceColumn property set to the LineageID value from step 9.

15. Also, each new Qty containing column needs to have the PivotKeyValue property set to the exact value that will show up in the Product column at run time. (Note: If the Product column is from a fixed width source (not varchar) then it may contain trailing blanks to fill the full width.)

More information can be found here:
Ashvini Sharma: A SQL Server Integration Services Blog
Configuring the Pivot component