Search code examples
ssissql-server-data-toolsunpivot

unpivot with different destination columns in SSIS Dataflow

I've got a pivot-table like this;

DAccount, DObject, DDep, DSum, CAccount, CObject, CDep, CSum
4851,301,43,201407.42,4813,121,55,-201406.42

and I like to unpivot it so it will look like this;

Account, Object, Dep, Sum
4851,301,43,201407.42
4813,121,55,-201406.42

I've tried searching for tips on unpivot, but all examples uses same destination column. I think its the Pivot Key Value I have problem understanding.
I've set destination columns to Account, Object, Dep and Sum (Account on both DAccount and CAccount etc). In the Pivot Key value its the default, ie same as input column.
When I press ok on this its says Key values need to match exactly, and I tried changeing them but then it says they need to be unique.

All help is appreciated!

Solution

  • You need to unpivot one column_name with its value by one, in a row.

    For example, this would put the two column names "nationality_1", and "nationality_2" into one string column "column_name", and the "value" column will be the new column showing their value row by row, with the person_id next to each value.

    enter image description here

    If you do the same for each of the four columns and merge join them on the person_id, you can reach the aim.

    In the example, that would mean to replace "value" with 1. "Account" and replace "nationality_1" with "DAccount" and "nationality_2" with "CAccount", and the "column_name" at the bottom can stay as it is.

    Do the same for the three other "Object", "Dep", "Sum". You will get a structure that looks like this:

    person_id column_name Account
1         DAccount    4851
2         CAccount    4813

    .. and so on for the rest of the three.

    You can then merge join the four on the "person_id" and the first letter of the column name.

    person_id column_name_first_letter Account Object Dep Sum
1         D                        4851    301    43   201407.42
2         C                        4813    121    55  -201406.42