SSIS Pivot Transformation

[[articles:SQL Server Integration Services (SSIS)|Integration Services]] uses transformations to manipulate data during an [[articles:Extract Transform Load (ETL)|ETL]] dataflow. Transformations can be used in [[articles:SSIS Dataflow Task|Data Flow Tasks]], between data source and destination components, or [[articles:SSIS: List of Transformations|other transformations]].

There are many cases (for example, in reporting), when some elements of a dataset has to be transposed. The Pivot transformation can help in this during the load process of a data warehouse.

For example, if the source dataset looks like this (three columns):

Then, with using the Pivot transformation, it can be transposed like this:

The transformation does not have a simple editor. It can be configured in the Advanced Editor of the transformation.
In this case, in the Input Columns page of the Advanced Editor, all columns had been checked in:

On the Input and Output Properties page, the PivotUsage of all input columns had been changed: 1 for Period, 2 for Metric Name and 3 for Amount. The options are the following:

• 0: The column is not pivoted, and the column values are passed through to the transformation output.
• 1: The column is part of the set key that identifies one or more rows as part of one set. All input rows with the same set key are combined into one output row.
• 2: The column is a pivot column. At least one column is created from each column value.
• 3: The values from this column are placed in columns that are created as a result of the pivot. 

All output columns must be configured and mapped to one of the original columns manually. This can be done by setting the PivotKeyValue and the SourceColumn property of all new columns:

• for the PivotKeyValue property, a possible value of a pivot input column must be specified.
• or the SourceColumn, the value of the LineageID of the input column have to be specified (which has a PivotUsage value of 3). The data type of the new column will be automatically set.

In this case, for the new column called Profit, the PivotKey value needs to be Profit, and the SourceColumn value is 111, which is the LineageID value of the Amount input column:

 This transformation supports an input, a regular and an error output. It looks like this in the data flow editor of BIDS (PVT Pivot):

This is a partially blocking transformation because of the introduction of completely new buffers.

Since there is no one-to-one relationship between the input and output buffers, this is an asynchronous transformation.

So, the Pivot transformation can be used as an opposite transformation of the [[articles:SSIS Unpivot Transformation|Unpivot transformation]], which is helpful when a more normalized result set is needed.

For more detailed information on this transformation, please refer to this MSDN article.
For the complete list of SSIS transformations, check [[articles:SSIS: List of Transformations|this Wiki article]].

Other Languages

This article is also available in the following languages:

• SSIS Pivot transzformáció (hu-HU)