Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

How To Use the Unpivot Data Flow Transform in SQL Server Integration Services SSIS

MSSQLTips author Ray Barley By:   |   Read Comments (10)   |   Related Tips: More > Integration Services Data Flow Transformations

Problem
I have some sales forecast data that I get from the business users in an Excel spreadsheet.  I need to load this data into a SQL Server database table.  The forecast contains product categories on the rows and the sales forecast for each month is on the columns.  I'd like to use SQL Server Integration Services (SSIS) to perform this recurring task.  How can I transform this data into a table that has Category, Month and Sales Forecast columns? 

Solution
SSIS has a Data Flow Transformation called Unpivot which can do exactly what you need.  Let's assume that your spreadsheet looks like this:

Although not shown above, there are 12 columns for forecast data, January through December.  Assume we want to load the data from the Excel spreadsheet into the following table:

CREATE TABLE [dbo].[SalesForecast](
 [ForecastDate] [datetime] NULL,
 [SalesForecast] [int] NULL,
 [CATEGORY] [nvarchar](255) NULL
)

We'll create a simple SSIS package to process the Excel spreadsheet.  The package will have the following Data Flow:

The following are the main points about the above data flow:

  • The Excel Source reads in the Excel spreadsheet.

  • The Unpivot transform takes the forecast value columns and transforms them into rows.

  • The Script Component takes the column names (i.e. JAN, FEB, etc. which are also transformed from columns to rows) and prepends the ForecastYear package variable to create a string value with the format of YYYY-MM-DD.  This can be inserted into a column of type DATETIME.

  • Insert Into SalesForecast is an OLE DB Destination that inserts rows into the SalesForecast table.

Before we start the explanation of the Unpivot, let's add a data viewer after the Unpivot and run the package.  The data viewer allows us to see the contents of the data flow.  To add a data viewer, right click the line connecting the Unpivot and Script Component, select Data Viewers, Add, then Grid.  The data viewer output is shown below:

As you can see the Unpivot component has performed the transformation that we need, taking the Sales Forecast values in the columns and turning them into rows.

The Unpivot Transformation Editor is shown below:

The Available Input Columns grid contains the list of columns in the data flow as read from the spreadsheet.  The CATEGORY column has Pass Through checked which means the column value simply passes through this component unchanged.  You can see the CATEGORY column in the data viewer output above.  The columns that have the checkbox to their left checked are unpivoted; i.e. these columns become rows.  All of the checked input columns are being transformed to the SalesForecast column, one per row, as shown in the Destination Column above.  Referring back to the data viewer output, you can see the SalesForecast column.  The Pivot key value column name is a new column that is added to the data flow; the value of this column is specified in the Pivot Key Value column.  The Pivot Key Value allows you to specify the value of your choice for each column in the original spreadsheet.  The Pivot Key Value shown is the first day of the month specified in the Input Column.  You can see the MonthDay column in the data viewer output above.

The Script Component has the following single line of code that prepends the package variable ForecastYear to the MonthDay column in the data flow to assign a string value to the output column ForecastDate that can be implicitly converted to a DATETIME:

        Row.ForecastDate = _
            Me.ReadOnlyVariables("ForecastYear").Value.ToString() + _
            "-" + Row.MonthDay

To test executing the package, use the following DTEXEC command line, specifying a value for the ForecastYear package variable (add the appropriate path before the package name):

DTEXEC /f unpivot_sample.dtsx /set \package.variables[ForecastYear];2010

Note that there is a semi-colon separating the package variable and the value.  Also the variable name (ForecastYear) is case-sensitive.

Next Steps

  • It's quite common to get data that needs to be transformed similar to this example, especially when the data is created by business users in an Excel spreadsheet.  The Unpivot data flow transformation can be used to simplify this task.
  • Download the sample SSIS package and experiment with it. 


Last Update: 5/28/2009


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, December 16, 2013 - 4:46:51 PM - Namagiri Read The Tip

Thanks!


Friday, May 17, 2013 - 9:55:56 AM - Raymond Barley Read The Tip

You can try this tip as a starting point for programmatically determining the columns in your Excel sheet: http://www.mssqltips.com/sqlservertip/1674/retrieve-excel-schema-using-sql-integration-services-ssis/  The idea is that for any given sheet you can populate a table where you have a row for every column in the sheet.  Once you have that you can craft some dynamic SQL to do whatever processing you need.  


Friday, May 17, 2013 - 3:12:22 AM - Padmashini Read The Tip

The link which you have given is for SQL Tables. But I am using Excel file as the source. Can you suggest any other soultion.


Thursday, May 16, 2013 - 3:34:23 PM - Raymond Barley Read The Tip

I don't see any way that the Unpivot Data Flow Transform could handle dynamic columns.  You would have to use dynamic SQL and the T-SQL UNPIVOT.  Take a look at this example: http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

 


Thursday, May 16, 2013 - 9:09:34 AM - Padmashini Read The Tip

The excel which used in this example is for fixed columns. Could you please tell me for dynamically growing columns.


Saturday, December 29, 2012 - 2:08:54 PM - Raymond Barley Read The Tip

To understand where MonthDay comes from take a look at the Unpivot Transformation Editor screen shot.  Each input column (e.g. Jan, Feb, etc.) gets transformed into a row; the editor allows you to specify a value for each input column (PivotKeyValue e.g. Jan = 01-01) and a column name to put this value; i.e. Pivot key value column name = MonthDay.

To think about this another way if here is a table, some test data, and an example of the T-SQL UNPIVOT; maybe this will make things more clear:

create table dbo.ForecastInput
(
    category    varchar(50)
,    jan            int
,    feb            int
,    mar            int
,    apr            int
,    may            int
,    jun            int
,    jul            int
,    aug            int
,    sep            int
,    oct            int
,    nov            int
,    [dec]        int
)

insert into dbo.ForecastInput values
('Bikes', 100,200,300,400,500,600,700,800,900,1000,1100,1200)

insert into dbo.ForecastInput values
('Components', 1,2,3,4,5,6,7,8,9,10,11,12)

insert into dbo.ForecastInput values
('Clothing', 1,2,3,4,5,6,7,8,9,10,11,12)

insert into dbo.ForecastInput values
('Accessories', 1,2,3,4,5,6,7,8,9,10,11,12)

select * FROM dbo.ForecastInput

declare @forecast_year char(4) = '2013'

SELECT category,
       case ForecastMonth
            when 'jan' then @forecast_year + '-01-01'
            when 'feb' then @forecast_year + '-02-01'
            when 'mar' then @forecast_year + '-03-01'
            when 'apr' then @forecast_year + '-04-01'
            when 'may' then @forecast_year + '-05-01'
            when 'jun' then @forecast_year + '-06-01'
            when 'jul' then @forecast_year + '-07-01'
            when 'aug' then @forecast_year + '-08-01'
            when 'sep' then @forecast_year + '-09-01'
            when 'oct' then @forecast_year + '-10-01'
            when 'nov' then @forecast_year + '-11-01'
            when 'dec' then @forecast_year + '-12-01'
        end Forecast_month, forecast
FROM (SELECT category, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec]
      FROM dbo.ForecastInput) f1
UNPIVOT (Forecast FOR ForecastMonth IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, [dec])) AS f2;


Friday, December 28, 2012 - 2:32:32 PM - Jeremy Kadlec Read The Tip

Jacob,

Thank you for the post and sorry for any issues you experienced.

Can you please outline your questions?

Can you also please provide the error message you received?

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, December 28, 2012 - 2:22:19 PM - jacob Read The Tip

I didn't find this very helpful at all. The download file wasn't very helpful either. I've just spent over an hour trying to figure this out. I have lots of questions like where did MonthDay come from.  Plus it would not execute.


Sunday, February 19, 2012 - 11:23:06 PM - Payawcripz Read The Tip
This is great but I want to add a clmoun to the links table and not sure how to modify the code to do this. I've tried the code below and it doesn't work, can anyone help?

Saturday, February 18, 2012 - 10:44:43 AM - Hanna Read The Tip
I don't know enguoh about SQLLite for WP7 to have an opinion on how to balance that with Sterling. I've used Sterling a lot and like it a lot and that would be my db story until Mango. After Mango . I'm not sure. There may still be a role for Sterling (I'm sure Jeremy will be blogging about that and I'll have back him on



 
Sponsor Information