A pivot table summarizes data selected from a table and groups it in a meaningful way. Selected column values become headings in the pivot table.

Consider the following table:

A pivot table lets you display columns of data grouped down the left side by name, with values in the itm column as headings, and each column showing the aggregation of val.

Here is an example pivot table based on the above data:

The PIVOT is a SQL table reference and can be given a correlation variable.

PIVOT detects what column or columns to be grouped by so the GROUP BY can be used but is redundant.

PIVOT has the following format:

where:

More than one pivot can be specified at a time and the pvt can be used to identify which columns come from which pivot, as shown in Range Pivoting.

Consider the following table:

The following creates a pivot table in which sales for each country is summarized for each year. By default, the column heading will be the data element chosen but as with ordinary result columns these can be renamed with an AS alias to give a more appropriate name if necessary.

The following example uses ranges to collect data values from the yr column and uses two pivots to generate both a sum and an average. Note that the result columns are both renamed and reordered.

In the previous example, we used the NOT IN clause to define a left-over bucket to aggregate the values falling outside of the specified buckets.

It is possible to list a data bucket but then not output that result; these will still be “IN” the data collection and hence be excluded from any NOT IN aggregation.

The example also uses the TO keyword to indicate that a range of values are to be clustered together. Notice that the TO value (the upper bound) is not included in the bucket. This simplifies the specification of non-overlapping values, so values 2002 TO 2007 will be greater than or equal to 2002 but strictly less than 2007. This example uses integer values, so the convention may seem odd but will make more sense when using data buckets that are less discrete, such as with floating point boundaries or other sortable data types.

For convenience, multiple range buckets can be specified at one time using the shorthand of 2003 TO 2006 TO 2009 TO 2012 instead of 2003 TO 2006, 2006 TO 2009, 2009 TO 2012.

For simplicity, the examples above use numeric columns for the pivot but any sortable data type can be used, such as string, date, timestamp, and time interval columns.

The results can also be referred to in selection expressions if needed, but in that case the select expressions will need to be placed in the HAVING clause as they will be referring to the result set. The following example shows these in use (no heading aliases):