Pivot and UnPivot with SSIS

The next step is to configure the output columns.

By clicking Add column button, ProductCategory, Qtr1, Qtr2, Qtr3 and Qtr4 are added. We have to configure the SourceColumn and PivotKeyValue attributes for those four columns.

For the ProductCategory column, assign LineageID of the Name input column to the SourceColumn. In this sample, it is 1161. For the Qtr1 column, assign LineageID of the OrderQty input column to the SourceColumn, which is 1166 in this example. PivotKeyValue is the column value of the initial data set. For Qtr1, we need records which has the value 1 for intQtr. So the PivotKeyValue for Qtr1 is 1.

Similary, Qtr2 will have 1166 for the SourceColumn and 2 for the PivotKeyValue. Qtr3 will have 1166 for the SourceColumn and 3 for the PivotKeyValue. Qtr4 will have 1166 for the SourceColumn and 4 for the PivotKeyValue.

Here we have also added another data viewer after the pivot transformation. Following is the output of the data viewer, thus achiving our goal.

The following depicts the final layout for the package.

We can extend this package to view Months from Quarters. For this, we need to change the derive column transformation. The following shows how this can be done.

MONTH(OrderDate) == 1 ? "Jan" : MONTH(OrderDate) == 2 ? "Feb" : MONTH(OrderDate) == 3 ? "Mar" : MONTH(OrderDate) == 4 ? "Apr" : MONTH(OrderDate) == 5 ? "May" : MONTH(OrderDate) == 6 ? "Jun" : MONTH(OrderDate) == 7 ? "Jul" : MONTH(OrderDate) == 8 ? "Aug" : MONTH(OrderDate) == 9 ? "Sep" : MONTH(OrderDate) == 10 ? "Oct" : MONTH(OrderDate) == 11 ? "Nov" : MONTH(OrderDate) == 12 ? "Dec" : "UNK"

The sorting was done for the Month column in the Sort transformation.