The Unpivot Transformation

[James Beresford]

Fig 1: The Unpivot Transformation


In this post I will be covering the Unpivot Transformation, the normalising sister component of the Pivot Transformation. Fortunately it is much easier to use. The sample package can be found*here for 2005 and guidelines on use are here

What does the Unpivot Transformation do?

This bit is fairly straightforward- it takes column values and converts them to rows, moving a value as it does so. So if your data looked like this:

Fig 2: Pre Unpivot Data


Unpivoting the columns containing product related data into a single column turns it into this:

Fig 3: Post Unpivot Data


This is the exact reverse of the Pivot Transformation. Like the Pivot Transformation it cannot aggregate, but in this case neither can its T-SQL equivalent.

How to Configure the Unpivot Transformation

When you set up an Unpivot operation you have 4 things to configure:

Fig 4: Configuring the Unpivot Transformation


The first thing you need to set up is the name of the column into which your Pivot Key Values will be placed into - in my example, I’m placing the month names into a Month column. Next select all the columns you want to be Unpivoted in the “Input Column” column.

Then specify the name of Destination Column in each cell in the column - which is the name of the output column that you want the unpivoted Values to be placed into. Usually this will be the same for every row - there is a special case when this doesn’t apply which I will cover at the end of this section.

Finally for each column you specify the value that will be put in the Pivot Key column for the row generated for that Unpivot operation - it doesn’t necessarily have to match the column name. In my example i’m pivoting columns that have 3 letter month names, but I’m setting the output Pivot Key Value to the full month name. The only constraint here is that the keys must be unique by Destination Column. That’s all there is to it - much easier than the Pivot.

You can pivot out to multiple Destination columns, but there are constraints - namely, you must have the same sets of Pivot Key Values for each Destination Column. So in the example I include in the sample package (Data Flow 2) - for the two Destination Columns, H1_Units and H2_Units, both have the same set of Pivot Key values - H_M1, HM_2, HM_3, HM_4 ,HM_5 & HM_6.

What is the The T-SQL Alternative?

As with the Pivot Transformation, there is a T-SQL alternative, and I have a simple demo of this in Data Flow 3 of the sample package. Unlike the Pivot Operation, functionally the T-SQL operation doesn’t add much - it can’t aggregate, for example. However you can expect the T-SQL operation to be faster, and as it is a database operation, will not hit your SSIS server anywhere near as hard - so use it where possible.

Where should the Unpivot Transformation be used?

You use the Unpivot transformation to normalise data that has been given to you in a denormalised format. The most common examples of this tend to be spreadsheets or mainframe reports by date where the months are columns, which look good for reporting but are a pain for efficient data storage.

MSDN Documentation for the Unpivot Transformation can be found here for 2008 and here for 2005.

MSDN Documentation for the T-SQL Unpivot operation can be found here for 2008 and here for 2005.



More...