Pivot table


A pivot table is a table of statistics that summarizes the data of a more extensive table. This summary might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way.
Pivot tables are a technique in data processing. They arrange and rearrange statistics in order to draw attention to useful information.
Although pivot table is a generic term, Microsoft trademarked PivotTable in the United States in 1994.

History

In their book Pivot Table Data Crunching, Bill Jelen and Mike Alexander refer to Pito Salas as the "father of pivot tables". While working on a concept for a new program that would eventually become Lotus Improv, Salas noted that spreadsheets have patterns of data. A tool that could help the user recognize these patterns would help to build advanced data models quickly. With Improv, users could define and store sets of categories, then change views by dragging category names with the mouse. This core functionality would provide the model for pivot tables.
Lotus Development released Improv in 1991 on the NeXT platform. A few months after the release of Improv, Brio Technology published a standalone Macintosh implementation, called DataPivot. Borland purchased the DataPivot technology in 1992 and implemented it in their own spreadsheet application, Quattro Pro.
In 1993 the Microsoft Windows version of Improv appeared. Early in 1994 Microsoft Excel 5 brought a new functionality called a "PivotTable" to market. Microsoft further improved this feature in later versions of Excel:
In 2007 Oracle Corporation made PIVOT and UNPIVOT operators available in Oracle Database 11g.

Mechanics

For typical data entry and storage, data usually appear in flat tables, meaning that they consist of only columns and rows, as in the following portion of a sample spreadsheet showing data on shirt types:
While tables such as these can contain many data items, it can be difficult to get summarized information from them. A pivot table can help quickly summarize the data and highlight the desired information. The usage of a pivot table is extremely broad and depends on the situation. The first question to ask is, "What am I seeking?" In the example here, let us ask, "How many Units did we sell in each Region for every Ship Date?":
A pivot table usually consists of row, column and data fields. In this case, the column is Ship Date, the row is Region and the data we would like to see is Units. These fields allow several kinds of aggregations, including: sum, average, standard deviation, count, etc. In this case, the total number of units shipped is displayed here using a sum aggregation.

Implementation

Using the example above, software will find all distinct values for Region. In this case, they are: North, South, East, West. Furthermore, it will find all distinct values for Ship Date. Based on the aggregation type, sum, it will summarize the fact, the quantities of Unit, and display them in a multidimensional chart. In the example above, the first datum is 66. This number was obtained by finding all records where both Region was East and Ship Date was 1/31/2005, and adding the Units of that collection of records together to get a final result.
Pivot tables are not created automatically. For example, in Microsoft Excel one must first select the entire data in the original table and then go to the Insert tab and select "Pivot Table". The user then has the option of either inserting the pivot table into an existing sheet or creating a new sheet to house the pivot table. A pivot table field list is provided to the user which lists all the column headers present in the data. For instance, if a table represents sales data of a company, it might include Date of sale, Sales person, Item sold, Color of item, Units sold, Per unit price, and Total price. This makes the data more readily accessible.
Date of saleSales personItem soldColor of itemUnits soldPer unit priceTotal price
10/01/13JonesNotebookBlack825000200000
10/02/13PrinceLaptopRed435000140000
10/03/13GeorgeMouseRed68505100
10/04/13LarryNotebookWhite1027000270000
10/05/13JonesMouseBlack47002800

The fields that would be created will be visible on the right hand side of the worksheet. By default, the pivot table layout design will appear below this list.
Each of the fields from the list can be dragged on to this layout, which has four options:
  1. Report filter
  2. Column labels
  3. Row labels
  4. Summation values

    Report filter

Report filter is used to apply a filter to an entire table. For example, if the "Color of Item" field is dragged to this area, then the table constructed will have a report filter inserted above the table. This report filter will have drop-down options. When an option is chosen from this drop-down list, then the table that would be visible will contain only the data from those rows that have the "Color of Item= Black".

Column labels

Column labels are used to apply a filter to one or more columns that have to be shown in the pivot table. For instance if the "Sales person" field is dragged to this area, then the table constructed will have values from the column "Sales Person", i.e., one will have number of columns equal to the number of "Sales person". There will also be one added column of Total. In the example above, this instruction will create five columns in the table — one for each sales person, and Grand Total. There will be a filter above the data — column labels — from which one can select or deselect a particular sales person for the pivot table.
This table will not have any numerical values as no numerical field is selected but when it is selected, the values will automatically get updated in the column of "Grand total".

Row labels

Row labels are used to apply a filter to one or more rows that have to be shown in the pivot table. For instance if the "Sales person" field is dragged on this area then the other output table constructed will have values from the column "Sales person", i.e., one will have number of rows equal to the number of "Sales Person". There will also be one added row of "Grand Total". In the example above, this instruction will create five rows in the table — one for each sales person, and Grand Total. There will be a filter above the data — row labels — from which one can select or deselect a particular sales person for the Pivot table.
This table will not have any numerical values, as no numerical field is selected, but when it is selected, the values will automatically get updated in the Row of "Grand Total".

Summation values

This usually takes a field that has numerical values that can be used for different types of calculations. However, using text values would also not be wrong; instead of Sum it will give a count. So, in the example above, if the "Units sold" field is dragged to this area along with row label of "Sales person", then the instruction will add a new column, "Sum of units sold", which will have values against each sales person.
Row labelsSum of units sold
Jones12
Prince4
George6
Larry10
Grand total32

Application support

Pivot tables or pivot functionality are an integral part of many spreadsheet applications and some database software, as well as being found in other data visualization tools and business intelligence packages.
; Spreadsheets
;Database support
;Web applications
;Programming languages and libraries
Excel pivot tables include the feature to directly query an online analytical processing server for retrieving data instead of getting the data from an Excel spreadsheet. On this configuration a pivot table is a simple client of an OLAP server. Excel's PivotTable not only allows for connecting to Microsoft's Analysis Service, but to any XML for Analysis OLAP standard-compliant server.