Thursday, May 14, 2015

How to use Pivot Transform in SSIS

A pivot table is a result of cross-tabulated columns generated by summarizing data from a row format.
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjArhzq97UhuTYJtwOQsl28i4hyphenhyphenfWNngWtbw4auCmtUkyEiSb37DkTmavovyHnXwG1eDZm0hb0HmalxSKC3Jjf3Wm8HL33qYc3BvxDtBaWKB86UBwZqKLJbnUPrtqpaoUj-tR-oBlGN8UQN/s1600/pivot+and+unpivot+examples+in+sql+server.png
Create a Data Flow Task something similar to below using Pivot component.
Use this query to get the unpivot data


Select TransMonth for the Pivot Key. This is the column that represents your columns. Change the Set Key property to ProductName. This is the column that will show on the rows, and your earlier query must be sorting by this column. Lastly, type the values of [December],[November],[October],[September] in the “Generate pivot output columns from values” area and check the Ignore option above this text box. Once complete, click the Generate Columns Now button.

The output will be something similar to shown below
Cheers! Uma

No comments:

Post a Comment