Working with Pivot

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 shown as a value in the month column.

UnPivoted Data

Pivoted Data

Since there are more than one field to pivot let’s join Profit and Sales fields together first using tab a delimiter than pivot the data and split it again

Joining Data

Data after Pivoting

Splitting Pivoted Data

Working with very wide Pivots

Quite ofter it is much easier to prepare pivot mapping in excel and paste it. Here is now: