This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Unpivoting data in SSIS using the Unpivot transformation
If you've been given the output from a pivot table or a cube, you can collapse it back into its original state using the UNPIVOT transform in Integration Services.

Posted by Andy Brown on 07 April 2016

Using the UNPIVOT transform in Integration Services

The UNPIVOT transform allows to collapse a two-dimensional array of data back into a relational database! That sentence probably makes little sense - so read on.

The diagrams for this blog are taken from SSIS 2012, but the UNPIVOT transform has been there since 2005.

Our problem

Someone has given you the following pivot table to import:

The pivot table that you have to import.

Your mission is to turn it into this:

We want to show each row separately in a table.

The rest of this blog shows how to achieve this. You can try this out yourself using this Excel workbook.

Step 1 - Importing the Data

Start by creating a data flow task, with an Excel source based on the above workbook:

The Excel source should be based on the Pivot Table worksheet.

When you edit this source, you can preview its columns:

Double-click on the source to edit it, then click on the Preview... button as shown here.

You'll see that we have a lot of work to do!

The imported data looks nothing like we want it to!

Step 2 - the Unpivot Transform

Add an Unpivot transform:

You'll find the Unpivot transform here.

Connect this up, then double-click on the Unpivot transform to edit it:

Double-click on the transform to change what it does.

Here are the columns you'll see:

The columns coming from the Excel pivot table.

Compare that with the pivot table, and it's reasonably obvious what you need to do:

We need to change Sum of Spending to Store, F2 to 2014, F3 to 2015, F4 to 2016 and F5 to 2017.

To achieve this, first tell SSIS that you don't want to anything special with the Sum of Spending column (we'll rename it later, but as far as I can see you can't do this at this stage):

We'll unpivot the data for the years, but keep the Sum of Spending as it is.

You can now specify how each of the year columns should be treated, by following the numbered steps listed below this diagram:

Complete the dialog box as explained below.

The steps are:

For each column, say what value SSIS should use (eg the column F2 represents data for year 2014).
Give a name to this set of values, to determine the name of the column SSIS will create.
For each column, say what value is being stored. Here we're telling SSIS to create a single extra column called Spending.

Add a Union all transform with a data viewer beneath it to view the output from this Unpivot transform:

The data viewer will allow us to see what's coming out of the unpivot transform.

This is what you should see:

We're already nearly there - we just need to get rid of the null row added, and rename the Sum of Spending column.

Step 3 - completing the package

There may be more elegant ways to tidy up the package, but I've gone for:

A conditional split transform to lose the null row; followed by
A union all transform to rename the Sum of Spending column.

The final diagram looks like this:

The numbers show that the conditional split transform has rejected one row.

Here's what the conditional split transform looks like:

The transform splits off rows where the spending is null - these are then not included further in our package.

I've used the union all transform to rename the Sum of Spending column to Store (there may be an easier way to do this):

The output column name has been changed.

And that's the unpivot transform in Integration Services!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR