New sql features

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:

Console

Execute

SELECT maker, type 
FROM product

select maker, type from product

    Scheme

No scheme

The result is the following table:

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

M
a
k
e
r
s

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.

Content:

PIVOT operator
UNPIVOT operator
PostgreSQL CROSSTAB