The Pivot transformation is one of the most useful transformations in SSIS, and also the biggest pain to configure. It comes with one editor, the Advanced Editor, so no pretty GUI here. Let’s walk though an example and hopefully it will help you learn how to use pivot. First off, let’s define the source data and the destination of our pivoted data. We will worked from a small set of survey data where each person was asked four questions, see the table below.
| Question | Answer | SurveyID |
| Name | EricJohnson | 1 |
| Height | 5'11" | 1 |
| Favorite Color | Red | 1 |
| Age | 31 | 1 |
| Name | Josh Jones | 2 |
| Height | 5'8" | 2 |
| Favorite Color | Black | 2 |
| Age | 34 | 2 |
| Name | Eric Beehler | 3 |
| Height | 5'10" | 3 |
| Favorite Color | Green | 3 |
| Age | 35 | 3 |
As you can see, there are 3 surveys, each with four questions. We want to pivot this data so that there is one row of data for each survey and the answers are in columns, like the table below.
| SurveyID | Name | Height | FavoriteColor | Age |
| 1 | EricJohnson | 5'11" | Red | 31 |
| 2 | Josh Jones | 5'8" | Black | 34 |
| 3 | Eric Beehler | 5'10" | Green | 35 |
This is where our Pivot Transformation comes into play. Each source column needs to have a PivotUsage assigned to it in order to tell SSIS how to handle the column during the pivot. The valid values are as follows.
- 0 - The column is not pivoted
- 1 - The column is part of the set key. This identifies the grouping of data. In our example, SurveyID will be a 1 to define one row for each survey.
- 2 - The column is a pivot column. The data in this column will be used to provide the new column names. In our example, each question will become a column, so Question will have a value of 2
- 3 - The values from this column are placed in columns that are created as a result of the pivot. In our case, each Answer will be placed in the new question columns, so Answer will have a value of 3.
Now that we have the PivotUsage values, we can configure the Pivot Transformation. When you open the Pivot transformation, the first thing you need to do is define the input columns that you want to work with. This is doen on the Input Columns tabs, as shown. As you can see, we have selected to work with Question, Answer, and SurveyID. We are ignoring the id column as it is a tracking column in the source table and not needed in our destination.
Next we switch to the Input and Output Properties tab. Here we need to expand Pivot Default Input and then Input Columns. Now we need to set the PivotUsage value for each column as we previously discussed. Question will be a 2, Answer will be 3 (as shown), and SurveyID will be 1.
Now here is where it gets a little weird. We have to add columns to output for each column that will exist AFTER the pivot. In our case, we will have the five columns we saw earlier in our example destination. To add the columns, expand Pivot Default Output and select Output Columns. Next, click the Add Column button 5 times. Then you will need to go through each column and set up a more logical name than the default of Column, Column 1, etc… Select each column and change the Name property so you end up with five columns named SurveyID, Name, Height, FavColor, and Age, as shown below (Note that I called the Favorite Color column FavColor to save space).
So now we have an input and an output, now we need to connect the two. This is done by setting the SourceColumn property on each output column to the LineageID property of the correct input column. This tells the transformation where each output columns will get its data. Obviously, the first output column, SurveyID, comes from SurveyID. The value for the other four output columns all come from Answer input column. So for SurveyID, we fine the input column LineageID on the property page here:
Now you will need to write down that value or remember it and then manually enter it into the SourceColumn property of the SurveyID output column here:
Repeat this step and enter the LineageID from the Answer input column into each of the other output column’s SourceColumn property.
We are almost there, but we have one more step. For each of our new output columns, Name, Age, FavColor, and Height we need to tell them which values in the Question column signify that the data belongs in each column. This is done by setting the PivotKeyValue for each of these columns to the actual text in the Answer column that’s means this answers is for the question contained in this column. So for the Name column, we enter “Name” into the PivotKeyValue, as shown. This tells the transformation to take the value from the Answer input column and place it in our Name output column anytime it encounters the text “Name” in the Question column.
That’s it, now we can work with the pivoted data in our SSIS package just like any other data set. I know that’s a lot and it is very confusing, but if you play with it a few times you will get it. Good luck and happy coding.