http://www.sqlservercentral.com/blogs/mikedavissql/2013/06/17/pivoting-weird-data-in-ssis-sort-of/

Printed 2014/07/24 11:50AM

Pivoting weird data in SSIS – Sort of…

By MikeDavis, 2013/06/17

Sometimes we get data in some bad forms. For example look at the below table:

clip_image001

The names and the amounts are comma separated in two different columns. Let’s imagine we need to get this data into a table like the one below:

clip_image002

Notice the column names are the names that were in the data. This can be accomplished with derived columns and conditional splits in an SSIS data flow.

Here is an example of a data flow that accomplishes this:

clip_image004

After the source the first component is the conditional split.

clip_image006

The conditional split sends the rows down different paths based on the number of commas in the data. This makes the derived columns afterwards much easier to handle. If not then we would have to nest a bunch of conditional statements in the derived columns.

The next steps are the derived columns. These will do different work based on the number of commas in the data.

With only one name in the data:

clip_image007

Two Names:

clip_image009

Three Names:

clip_image011

Four Names:

clip_image013

In these derived columns you are taking the items between the columns and separating them into their own columns. Here is a data viewer showing the data after the union all.

clip_image015

In the next step I decided to get rid of the nulls so the last derived column would be easier to write. If you want to leave in the null you can, but then the last derived columns will need to have a lot of ISNULL checks.

clip_image016

Now that the nulls are gone, and you have everything divided into individual columns, you can use one more derived column to arrange the data into the correct columns.

clip_image018

And now the mapping to the destination table should be easy. The ID column is mapped to ID and the names are mapped to their correct columns.

clip_image019

This method will work if you have a limited number of columns. The maintenance on this would be a headache if you have to add or remove names frequently. I would not suggest this method if you have frequent column changes.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.