Русский    English

PIVOT and UNPIVOT Operators

I would start with EXCEL spreadsheets to explain what a PIVOT is. In MS Excel 5.0 new functionality was introduced called a 'PivotTable'. Pivot tables are two-dimensional visualization of multidimensional data structures applied in OLAP technology for data warehousing. More correctly say it is two-dimensional sections of three-dimensional OLAP-cubes, if we keep in mind existence of element called “page” on pivot table. You can do common operations with pivot tables under multidimensional structures, for example, mentioned above cube sectioning, consolidation and detail - deconsolidation operation.

It should be said that the pivot table is not a relational, because not only has the column headers, but also has the row headers, while both are formed from data stored in the columns of usual relational tables. It means that numbers of rows and columns is unknown, because they are formed dynamically by querying relational data. In addition, the headers may be multi-level sub-headings that enable the operation of consolidation (move to a higher level of hierarchy) and detail (move to a lower level of the hierarchy).

Such properties allow using pivot tables along with pivot charts in EXCEL as a client to visual displaying of multidimensional data stored in data warehouses that are supported by different DBMS (for example MS  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server Analysis Services).

To explain above statements, let's consider a query to one of the training databases in the sql-ex.ru:

  1. SELECT maker, type
  2. FROM product

The result is the following table:

maker type
A Printer
D Printer
A Laptop
C Laptop
A Printer
A Printer
D Printer
E Printer
B Laptop
A Laptop

Suppose now we need to get a table with the following headings:

       Type of products

      Laptop  PC   Printer

Row headers here are the unique names of makers, which are taken from column ‘maker’ above query, and column headers - unique types of products (from column ‘type’). And what should be in the middle? The answer is obvious – some aggregation, for example, function count(type), which will calculate number of PC, laptops, printers separately for each maker and fill appropriate cells in table.

This is simple example of pivot table, with only two levels of hierarchy in columns and rows. I.e. performing the consolidation on vertical we obtain the number of each type of product for all makers, and on horizontal – the total number of models regardless of the type for each maker. One could add levels of hierarchy, such as color printers, laptop screen size, etc.

Now you know what the pivot table is.

Also, one can say that the pivot-table in SQL is a one-level summary table.

PIVOT operator is not a standard (I'm not sure that it will ever be standardized due to non-relational core). Therefore I will use its implementation in examples in language T-SQL (SQL Server 2005/2008).

I can make mistakes in chronology, but it seems to me that the success of implementation of a PivotTable in Excel has led to so-called cross-queries in Access, and finally to the PIVOT operator in T-SQL.

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100