Pivot and UnPivot with SSIS

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.

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |