Pivot Transformation

Pivot transformation converts rows into columns. I see most of the people are not aware of setting the transformation properties.

Here is a small example.

My source data looks like this




Pivot transformation organizes data as below.



To implement this, add a Data flow task. Take OLE DB Source and select the source table. Add a pivot transformation to it. Select all the three columns in the input columns tab. In the Input and Output Properties tab give 1 (setkey) as the pivot usage for customers, 2 (PivotKey) for product and 3 (Pivotvalue) for Qty. Note down the LineageID of customer and Qty.

In the Pivot Default output add a column for customer and give the SourceColumn as the LineageID of the customer in the input column.

Add columns for product. Since there are 6 products add 6 new columns and give them appropriate names. For ChairQty mention Pivotkeyvalue as Chair and the SourceColumn is the LineageID of the qty input column.

Repeat this for the other 5 columns. Add an OLE DB destination to this pivot transformation. When executed rows will be organized into columns.