Unpivot Transformation
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.
The following diagram shows a data set after it has been unpivoted on the Product column.
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.
Configuration of the Unpivot Transformation
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 (SSIS) Expressions, Use 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 Set the Properties of a Data Flow Component.