Pivot Transformation in SSIS

Pivot is a mechanism where we are interchanging the row into the column.

Let’s see the example

   

Here we have a table which is having 3 columns. Now with the help of Pivot we convert row to columns.

Number of column= Unique value of the row

In the given table there are 4 unique values (Q1, Q2, Q3 and Q4). So in the Pivot table there are 4 columns.

Let’s learn how to use Pivot transformation in SSIS

Open the SSDT.

Take the data Flow task

Double click on DFT

Taking the Source transformation as OLEDB source

  

Creating the connection

  

Mapping the column

  

Click ok.

Now I am taking the Pivot transformation

   

Double click on the Pivot transformation

  

Set Key

This key identifies a group of input rows that will get pivoted into one output row. For example: CompanyID

Note: The Input data must be sorted on this column.

Pivot Key

Values in the input data from this column will become new column names in the output.

Pivot Value

Value from this column will be mapped in to the new pivot output column.

Generate pivot output column from value

Here we write the new column name.

After that click on the Generate columns Now Button. The new column value will be seen in Existing Pivoted output columns.

After that click on Ok.

Taking OLEDB destination

  

Creating the connection

  

Mapping the column values

   

Click Ok

Now it is ready to run.

  

Package executed successfully.

Let’s see the result. In database

Finally we get the desirer output.