Pivot Tables

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.

Table 1. Input Data
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:

Table 2. Pivot 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.