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

By:   |   Updated: 2009-05-28   |   Comments (12)   |   Related: 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:

input data

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:

dataflow

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:

data viewer

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:

unpivot editor

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. 


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2009-05-28

Comments For This Article




Saturday, January 14, 2017 - 1:41:48 PM - Ray Barley Back To Top (45302)

Replying to KMurphy below - here are some thoughts

The Pivot key value column name is the "destination" column for each Pivot Key Value.  I think each Pivot Key Value should be the same column type and size.  Cast each source column to be nvarchar(100); i.e. the largest of the Pivot Key Value columns in the source.

I found another article that you may want to review; it's similar to mine but I think more clear: http://www.bradleyschacht.com/how-to-use-the-unpivot-transform-in-ssis/

 

 

Wednesday, January 11, 2017 - 3:54:36 PM - KMurphy Back To Top (45221)

 Do all the pivoted rows/columns have to be the same size?

 The pass thrus do not matter but can I unpivot columns 1 nvarchar(10), column 2 nvarchar(10) , and column 3 nvarchar(100)?

I seem to get an error on this matching metedata

Monday, December 16, 2013 - 4:46:51 PM - Namagiri Back To Top (27813)

Thanks!

Friday, May 17, 2013 - 9:55:56 AM - Raymond Barley Back To Top (24013)

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 Back To Top (24008)

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 Back To Top (23998)

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 Back To Top (23985)

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 Back To Top (21187)

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 Back To Top (21179)

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 Back To Top (21178)

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 Back To Top (16084)
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 Back To Top (16069)
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














get free sql tips
agree to terms