Pivot and UnPivot with SSIS

Next, we need to derive the Quarter. Even though we can modify the initial T-SQL to return the Quarter, I have used derive column data flow transformation task. The following expression is used to derive the Quarter.

MONTH(OrderDate) >= 1 && MONTH(OrderDate) <= 3 ? 1 : MONTH(OrderDate) >= 4 && MONTH(OrderDate) <= 6 ? 2 : MONTH(OrderDate) >= 7 && MONTH(OrderDate) <= 9 ? 3 : MONTH(OrderDate) >= 10 && MONTH(OrderDate) <= 12 ? 4 : 0

We now need to group the above data with Category and Quarter. We can use aggregate transformation and configure it to be grouped by Name and intQtr.

Next we need to add a sort transformation, and here I have used category to sort. We also need to sort the key column, otherwise pivot will not work properly. To see the data up to this point,you can add a data viewer.

Below is the scrennshot of the data set should be getting, which is the data set we need to pivot.

We have now reached the core part of this article- pivoting. For pivoting, there is a pivot transformation confirguration which is not exactly straight forward. At input tab of the pivot transformation, you need to select columns that you would use in the pivot operation, which in this case would be all three available columns.

The next most important tab is the ‘Input and Output’ properties tab, pictured below.

For input columns, we need to configure the pivot usage attribute.

Source: Books on line, SQL Server 2005

According to the above table, Name column should be Option 1 , intQtr should have Option 2 and OrderQty should have Option 3 for pivot usage attribute value.