Home Library Learn Downloads Support Community
Sign in | United States - English | |
This topic has not yet been rated Rate this topic

Unpivot Transformation

SQL Server 2008 R2

The Unpivot transformation makes an unnormalized dataset into a more normalized version by expanding values from multiple columns in a single record into multiple records with the same values in a single column. For example, a dataset that lists customer names has one row for each customer, with the products and the quantity purchased shown in columns in the row. After the Unpivot transformation normalizes the data set, the data set contains a different row for each product that the customer purchased.

The following diagram shows a data set before the data is unpivoted on the Product column.

Dataset after it is unpivoted

The following diagram shows a data set after it has been unpivoted on the Product column.

Dataset before it is unpivoted

Under some circumstances, the unpivot results may contain rows with unexpected values. For example, if the sample data to unpivot shown in the diagram had null values in all the Qty columns for Fred, then the output would include only one row for Fred, not five. The Qty column would contain either null or zero, depending on the column data type.

The Unpivot transformation includes the PivotKeyValue custom property. This property can be updated by a property expression when the package is loaded. For more information, see Integration Services Expression Reference, Using Property Expressions in Packages, and Transformation Custom Properties.

This transformation has one input and one output. It has no error output.

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in the Unpivot Transformation Editor dialog box, click one of the following topics:

For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

For more information about how to set the properties, see How to: Set the Properties of a Data Flow Component.

Integration Services icon (small)  Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.
Did you find this helpful?
(2000 characters remaining)
Community Content Add
Annotations FAQ
Behavior change between 2005sp4 and 2008R2
"Under some circumstances, the unpivot results may contain rows with unexpected values. For example, if the sample data to unpivot shown in the diagram had null values in all the Qty columns for Fred, then the output would include only one row for Fred, not five. The Qty column would contain either null or zero, depending on the column data type. "
For 2005 it was the Qty column would contain empty string or a zero depending on the column data type
For 2008 it is Qty column would contain NULL not depending on the column data type

In my opion, it should not produce a row for Fred because you now end up with a NULL value for product.
History
your example is horrible
It seems to me companies that build software will also provide a complicated example.  I assume this is presented to show the power of the tool.  In many cases, however, a simple solution is elegant and will explain the basic concepts.  When the example is complicated, I tend to turn away in favor of a more simple example elsewhere.  Why not describe this component with a most basic example such as a table of data having 3 columns pivoted into a single column?  Maybe include a link to an advanced area where this complex example may be studied?
History