Transforming data structure through pivots and unpivots

You can use pivot and unpivot transformations to change narrow tables into wide tables and vice versa. You can also use a pivot to permanently reduce the granularity of a data table by summing up to a higher level.

Take a look at the baseball player data. What sort of table do you think it is? It has columns for player name and team and then individual columns for a range of standard baseball statistics, with each statistic getting its own column. This was very convenient when we wanted to calculate the walk-to-strikeout ratio.

Unpivot

Let's see what happens when we unpivot the baseball player data table.

The data used here is BaseballPlayerData.xls, which you can download from http://www.insidespotfire.com.

1. Load the baseball data into an analysis file.

2. Open the Add Data Tables dialog and using the Add dropdown, select the baseball player data From Current Analysis. So far, we've been adding data from external sources, but it's also possible...