Pivoting converts rows into columns.We can perform this task using SSIS Pivot Transformation component.
SQL server 2012 added an UI to Pivot transformation component and makes it simpler. We can configure the pivot operation by setting the options in pivot dialog box.
Sample data: It pulls Total number of items sold by product
by month in 2013.
SQL:
SELECT DISTINCT DATENAME([month], SalesDate) AS [Month], productName, SUM(NumberSold) AS Total
FROM Product
GROUP BY DATENAME([month], SalesDate), productName
ORDER BY productName
Output:
We want to visualize data as shown below:
Steps:
Start| All Program| SQL Server 2012 |SQL
Server Data Tools.
Create a new project – File |New Project |Integration
Services Project.
Add “ Data Flow Task ”. Double click the “ Data Flow Task”.
Add “ OLEDB Data source”. Double click the “OLEDB data source” and set
connection and SQL command as shown below.
Add “Pivot Transform” from toolbox and connect “OLEDB data
source” to “Pivot Transform”. Double click the “Pivot Transform” to open the “Pivot
UI”. Set the “Pivot Key”, ”Set Key” and “Pivot Value”.
Under “Generate Pivot Output columns from Values”, enter the
month name and then click the “Generate Columns Now” button. Click “OK”.
Add “OLEDB Destination” and connect it with “Pivot Transform”.
Double click the “OLEDB Destination” and set connection and create a table to
output data.
Run the Package.
The package ran successfully. Now open SSMS and query the
table where you sent the data.
Output:
