The next key configuration is the Input and Output Configuration of the Pivot transformation. We will need to add all twelve columns for Month apart from the Product Category column.
The configuration is similar as in the first example. For example, the Jan column PivotKeyValue is Jan and the SourceColumn is LinegeID of OrderQty of InuputColumns.
Below depicts the final output of this package.
In both of these cases, we have used one column to pivot, which is ProductCategory. Now we will attempt to add two columns for pivoting – Year and Month.
We will need to add the Year column, which is YEAR(OrderDate) at derive column. Year column was included for Aggregate transformation. Sorting is done for Month and Year.
Let us see what we have to do for Pivot transformation. PivotUsage of intYear column is 1. Source column of Year output column LineageID of intYear input column.
Below depicts the output for the above package.
Unpivot
Now that we have reviewed pivoting, it is time to learn how to “Unpivot”. The unpivot transformation makes a de-normalized dataset into a more normalized version by expanding values from multiple columns in a single record into multiple records within the same values in a single column. It is the exact the opposite of pivoting.
We can begin with our earlier data format, which is in a comma separated value (csv) format.
2002,Accessories,,,,,,,204,353,313,117,166,128
2003,Accessories,51,75,80,100,138,156,215,381,386,120,131,188
2004,Accessories,40,76,75,91,188,178,,,,,,
2001,Bikes,,,,,,,148,519,383,213,660,492
2002,Bikes,206,516,445,234,635,500,751,1088,1005,646,968,881
2003,Bikes,580,887,680,746,937,753,876,1312,1333,878,1234,1332
2004,Bikes,758,1084,1033,940,1332,1336,,,,,,
2003,Clothing,,,,,,,890,1474,1700,829,1254,1610
2004,Clothing,732,1018,1447,890,1301,1606,,,,,,
2001,Components,,,,,,,453,764,575,667,1258,1007
2002,Components,618,1161,862,738,1282,946,1865,2526,1782,1468,2229,1556
2003,Components,1176,2029,1286,1454,2283,1485,1225,1562,1311,1082,1436,1408
2004,Components,965,1374,1170,1217,1597,1379,,,,,,
You can get the above data in a text file name PivotedData.txt, which you will find with the sample package.