Unpivot Transformation

  • Article

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.

Configuring the Unpivot Transformation

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, click one of the following topics:

See Also

Concepts

Pivot Transformation
Creating Package Data Flow
Integration Services Transformations

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added information about the results of unpivoting rows that contain null values.

5 December 2005
New content:
  • Added information about using property expressions with the PivotKeyValue custom property.
Changed content:
  • Updated number of outputs on transformation.