The Pivot transformation turns several rows of data into one, It makes a normalized data set into a less normalized but more compact version by pivoting the input data on a column value. For example, a normalized sales report includes shop name, month and sales amount usually has several rows for any shop, with each row for that shop showing sales amount for a different month. By pivoting the data on the month column, the Pivot transformation object can output a data with a single row per shop. That single row lists all sales by the shop, with month names shown as column names, and sales amount is shown as a value in the month column.
The following picture shows a data set before the data is pivoted:
Note:
When a dataset is pivoted, input columns perform different roles in the data transformation process. There are several possible options:
- The column acts as the key or part of the key that identifies a set of records.
- The column defines the pivot. The values in this column are associated with columns in the pivoted dataset.
- The column contains values that are placed in the columns that the pivot creates.
The following picture shows a data set after the data has been pivoted on the Month column.
To pivot data efficiently, which means creating as few records in the output dataset as possible, the input data must be sorted on the pivot column. If the data is not sorted, the Pivot transformation might generate multiple records for each value in the set key, which is the column that defines set membership. For example, if the dataset is pivoted on a Name column but the names are not sorted, the output dataset could have more than one row for each customer, because a pivot occurs every time that the value in Name changes.
Configuring the Pivot Transformation
- To change UnPivot properties double click on the object.
- Select appropriate Set Key, Pivot key and Pivoted value fields.
- Enter Pivot keys and Output Field names.
