Сводные таблицы: принципы построения и функциональность

Table that summarizes data from another table
cross tabulation that aggregates only by counting (rather than summing, averaging, etc.) A pivot table is a table of values which are aggregations of groups of individual values from a more extensive table (such as from a database, spreadsheet, or business intelligence program) within one or more discrete categories. The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision making. Although pivot table is a generic term, Microsoft held a trademark on the term in the United States from 1994 to 2020.

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 (Black, Red, and White in the example above). When an option is chosen from this drop down list ("Black" in this example), then the table that would be visible will contain only the data from those rows that have the "Color of Item= Black".

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 "Salesperson" field is dragged to this area, then the table constructed will have values from the column "Sales Person", i. e., one will have a number of columns equal to the number of "Salesperson". 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 salesperson, and Grand Total. There will be a filter above the data — column labels — from which one can select or deselect a particular salesperson 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 are used to apply a filter to one or more rows that have to be shown in the pivot table. For instance, if the "Salesperson" field is dragged on this area then the other output table constructed will have values from the column "Salesperson", i. e., one will have a 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 salesperson, and Grand Total. There will be a filter above the data — row labels — from which one can select or deselect a particular salesperson 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".

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.

PostgreSQL, an object–relational database management system, allows the creation of pivot tables using the tablefunc module. MariaDB, a MySQL fork, allows pivot tables using the CONNECT storage engine. Microsoft Access supports pivot queries under the name "crosstab" query. Microsoft SQL Server supports pivot as of SQL Server 2016 with the FROM PIVOT keywords
Oracle Database supports the PIVOT operation. Some popular databases that do not directly support pivot functionality, such as SQLite, can usually simulate pivot functionality using embedded functions, dynamic SQL or subqueries. The issue with pivoting in such cases is usually that the number of output columns must be known at the time the query starts to execute; for pivoting this is not possible as the number of columns is based on the data itself. Therefore, the names must be hard coded or the query to be executed must itself be created dynamically (meaning, prior to each use) based upon the data.

ZK, an Ajax framework, also allows the embedding of pivot tables in Web applications.

Programming languages and libraries suited to work with tabular data contain functions that allow the creation and manipulation of pivot tables. Python data analysis toolkit pandas has the function pivot table and the xs method useful to obtain sections of pivot tables. R has the Tidyverse metapackage, which contains a collection of tools providing pivot table functionality, as well as the pivottabler package.

Excel pivot tables include the feature to directly query an online analytical processing (OLAP) 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 (XMLA) OLAP standard compliant server.