Pivot Transformation

Pivot means to convert rows into columns

Step 1:

Click Windows button and go to SQL Server 2008 R2 and run as a administrator.

It shows  a bellow window.

Step 2:

Go to a views and create a new project ( Short cut of New Project is Ctrl + Shift + N).

Step 3:

Give a project name (e.g. IS) and click OK button and then shows a bellow window.

 Step 4:

To drag and drop the Data Flow Task

Step 5: 

To edit the Data Flow Task then it open a Data Flow

Step 6:

To dag and drop the OLEDB source and edit it.

Step 7:
Write a simple query as show in bellow.

Select
    Product.ProductID,
    Product.Name,
    year(SalesOrderHeader.OrderDate) as OrderYear,
    sum(SalesOrderDetail.OrderQty) as OrderQuantity
from Sales.SalesOrderDetail
inner join Production.Product
on SalesOrderDetail.ProductID=Product.ProductID
inner join Sales.SalesOrderHeader
on SalesOrderDetail.SalesOrderID=SalesOrderHeader.SalesOrderID
group by Product.ProductID,Product.Name,year(SalesOrderHeader.OrderDate)
order by Product.Name,year(SalesOrderHeader.OrderDate)

Step 8:
Drag and drop Pivot transformation and edit it.

Step 9:
Select all columns.

Step 10:

PivotUsage property for each input column will be as below:

ProductID           0
Name                  1
OrderYear           2
OrderQuantity     3

Step 11:
Go to input and output properties.

Step 12:

After setting input columns, go to Pivot Default output , and under output columns add these columns:

ID, Name , 2001 ,2002 , 2003 , 2004

Step 13:

select 2001 under output columns

this columns will show the results for the year 2001, so enter 2001 in PivotKeyValue property.

Set SourceColumn with lineageID of OrderQuantity input column.

In fact you must set SourceColumn property with the lineageID of the input column which has the PivotUsage 3 value.

in this example , lineageID of OrderQuantity is 17 (Remember you must use your own lineageIDs

Step 14:
lineageID of Name is 20 

Step 15:
lineageID of OrderQuantity is 26

Step 16:

We set SourceColumn with 26 in 2001 output column

We set SourceColumn with 26 in 2002 output column

We set SourceColumn with 26 in 2003 output column

We set SourceColumn with 26 in 2004 output column

Step 17:
Drag and drop flat file destination and edit it.

Step 18:
Browse a flat file in your PC

Step 18:
Execute task