A pivot table aggregates and transposes column values in the flow to make it easier to analyze data visually. With pivot, you can arrange input columns into a cross tabulation format (also known as crosstab) that produces rows, columns and summarized values. You can also use fields as input and not display them. You can use pivot to pivot on two dimensions or to group aggregate data on one dimension.
This example shows sales data for shirts.
Region | Gender | Style | Ship Date | Units | Price | Cost |
---|---|---|---|---|---|---|
East | Boy | Tee | 1/31/2010 | 12 | 11.04 | 10.42 |
East | Boy | Golf | 6/31/2010 | 12 | 13.00 | 10.60 |
East | Boy | Fancy | 2/25/2010 | 12 | 11.96 | 11.74 |
East | Girl | Tee | 1/31/2010 | 10 | 11.27 | 10.56 |
East | Girl | Golf | 6/31/2010 | 10 | 12.12 | 11.95 |
East | Girl | Fancy | 1/31/2010 | 10 | 13.74 | 13.33 |
West | Boy | Tee | 1/31/2010 | 11 | 11.44 | 10.94 |
West | Boy | Golf | 2/25/2010 | 11 | 12.63 | 11.73 |
West | Boy | Fancy | 2/25/2010 | 11 | 12.06 | 10.51 |
West | Girl | Tee | 2/25/2010 | 15 | 13.42 | 13.29 |
West | Girl | Golf | 6/31/2010 | 15 | 11.48 | 10.67 |
North | Boy | Tee | 2/25/2010 | 17 | 16.04 | 10.42 |
North | Boy | Fancy | 2/25/2010 | 12 | 11.56 | 12.42 |
North | Girl | Tee | 2/25/2010 | 16 | 12.32 | 18.42 |
North | Boy | Golf | 1/31/2010 | 18 | 11.78 | 13.23 |
North | Girl | Tee | 2/25/2010 | 12 | 18.45 | 11.64 |
North | Girl | Golf | 2/25/2010 | 14 | 11.23 | 19.85 |
North | Boy | Fancy | 1/31/2010 | 16 | 12.54 | 13.42 |
North | Girl | Tee | 2/25/2010 | 17 | 181.73 | 15.83 |
South | Boy | Fancy | 1/31/2010 | 19 | 14.15 | 13.42 |
South | Girl | Tee | 2/25/2010 | 11 | 11.85 | 12.92 |
South | Girl | Fancy | 1/31/2010 | 13 | 11.54 | 14.35 |
South | Boy | Tee | 2/25/2010 | 15 | 14.14 | 14.73 |
South | Boy | Golf | 2/25/2010 | 16 | 17.83 | 17.83 |
South | Girl | Fancy | 6/31/2010 | 11 | 18.24 | 12.35 |
South | Girl | Tee | 1/31/2010 | 20 | 19.94 | 12.95 |
South | Boy | Golf | 2/25/2010 | 12 | 21.25 | 19.56 |
We want to be able to determine how many units we sold in each region for every ship date. To do this, we use pivot to generate this table:
Region | 1/31/2020_ShipDate | 2/25/2020_ShipDate | 6/31/2020_ShipDate |
---|---|---|---|
East | 32 | 12 | 22 |
North | 34 | 88 | |
South | 52 | 54 | 11 |
West | 11 | 37 | 15 |
In this case, the column is Ship Date, the row is Region, and the data we would like to see is Units. The total number of units shipped is displayed here using a sum aggregation.