loading..
Русский    English
22:54

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  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. 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:

Console
Execute
  1. SELECT maker, type
  2. FROM product

The result is the following table:

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


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

       Type of products

M
a
k
e
r
s
      Laptop  PC   Printer
A
B
C
D
E


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
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100