PIVOT clause

Pivots a table expression in the FROM clause of a SELECT statement (FROM pivoted-derived-table) into a pivoted derived table. Pivoted derived tables offer an easy way to rotate row values from a column in a table expression into multiple columns and perform aggregation where needed on the columns included in the result set.

Syntax

FROM pivoted-derived-table

pivoted-derived-table :
pivot-source-table PIVOT [ XML ] ( pivot-clause ) [ AS ] pivoted-correlation-name

pivot-source-table : table-expression

pivot-clause : 
   aggregate-clause pivot-for-clause pivot-in-clause

aggregate-clause :
aggregate-function( [ aggregate-expression ] ) [ [ AS ] aggregate-alias ] [,...]

pivot-for-clause : 
FOR pivot-column
| FOR ( pivot-column [,...] )

pivot-in-clause : 
IN ( constant-expression [[ AS ] constant-expression-alias ] [,...] )
| IN ( ( constant-expression [,...] ) [[ AS ] constant-expression-alias ] [,...] )
| IN variable-name
| IN ( subquery-expression )
| IN ( ALL )
| IN ( ANY )

Parameters

XML clause
Specify XML to output the aggregates and pivot values in a new, single column in XML format. Use this clause when specifying the values of pivot-in-clause using a non-constant list of values. The XML clause is used in conjunction with the IN (ANY), IN (ALL), and IN (subquery-expression) clauses.
aggregate-function( [ aggregate-expression ] ) [ [ AS ] aggregate-alias ]
Specify any aggregate function that returns a single value for a set of rows.
- aggregate-function - Specify any aggregate function that returns a single value for a set of rows (also known as a scalar function).
- aggregate-expression - Specify the parameters of the aggregate function. The aggregate expression must reference only columns found in pivot-source-table.
  If the aggregate function allows it, then the ORDER BY and DISTINCT clauses can be specified. For example:
  LIST( DISTINCT DepartmentID ORDER BY Salary )
- aggregate-alias - Specify an alias for the aggregate function. The list of aggregate functions can have at most one aggregate function without an alias. Aggregate aliases, together with the IN clause aliases, are used to generate the names of the new columns of the pivoted derived table. As a best practice, always specify an alias for your aggregate.
FOR clause
Specify one or more columns on which to pivot the data. pivot-column must be a column in pivot-source-table. If you specify more than one pivot-column, then you must enclose them in parentheses.
IN ( constant-expression [[ AS ] constant-expression-alias ] [,...] )
Specify a set of constant expressions on which to pivot the data. Use this syntax when the FOR clause lists only one column, namely, FOR pivot-column.

If an alias is not specified, then the implicit alias is the string representing the constant expression. For example, the implicit alias for the constant 10 is '10'. As a best practice, always specify an alias for constant-expression. Implicit and explicit aliases for constant expressions in the IN clause, together with aggregate aliases, are used to generate the names of the new columns of the pivoted derived table.

Each new column found in the pivoted derived table corresponds to a pairing of an aggregate function and an IN item, and has a name that reflects the pairing. The first part of the name is the alias of the IN item, and the second part of the name (after the underscore) is the alias of the aggregate function. If a generated column name is an invalid identifier, then an error is generated and the statement fails.
IN ( ( constant-expression [,...] ) [ [ AS ] constant-expression-alias ] [,...] )
Specify a set of lists containing constant expressions on which to pivot the data. Use this form of the IN clause when you specify multiple columns in the FOR clause. The number of columns specified in the FOR clause must be equal to the number of items in any constant list of the IN clause.

If an alias is not specified, then the implicit alias is the string representing the list of constant expressions. For example, the implicit alias for the constant list (10, 20) is "(10, 20)". Implicit and explicit aliases for constant expressions in the IN clause, together with the aggregate aliases, are used to generate the names of the new columns of the pivoted derived table.
IN variable-name
Specify a variable that contains a set of constants on which to pivot the data. This form of IN clause is similar to the IN clause using constants. All the conditions described above for the IN clause with constants must hold for the IN clause using a variable.

The variable must be set when the statement containing the pivoted derived table is described, open, or run. The variable must be declared using an array type. The aliases for the IN clause are implicitly defined using the current values of the variable. For example, if the variable is the array (10, 20), the alias for first constant is "10", and the alias for the second constant is "20".

If the FOR clause has only one pivot column, the variable must be an array with elements that have a domain that is compatible with the data in pivot-column. For example, if the FOR clause is FOR DepartmentID, with a DepartmentID column of type INT, then a variable @var defined as an array of type INT can be used in the IN clause.

If the FOR clause has a set of pivot columns, then the variable must be an array of rows whose elements have compatible domains with the columns in the FOR clause. For example, if the FOR clause is FOR (DepartmentID, State), with a DepartmentID column of type INT, and State column of type CHAR(16), then a variable @var defined as an array of type ROW( X INT, Y CHAR(16)) can be used in the IN clause.
IN ( subquery-expression )
Specify IN ( subquery-expression ) to pivot on all values found by the subquery.

The XML column in the derived table has a name defined by the names of the columns in the FOR clause. It is an XML element containing a set of items, one item for each pair of values for pivot columns and aggregate aliases. The NULL values are encoded as empty string in the XML element.

If the FOR clause is FOR pivot-column (for example, COUNT(*) AS "COUNT" FOR DepartmentID ), then the format of each item in the XML column is:
```
<item>
<column name="DepartmentID">100</column>
<column name="COUNT">1</column>
</item>
```
Specify the XML keyword when using this form of the IN clause.
IN (ALL)
Specify IN (ALL) to pivot all values in pivot-source-table.

IN (ALL) is equivalent to IN (subquery-expression), where subquery-expression is pivot-source-table.

Specify the XML keyword when using this form of the IN clause.
IN (ANY)
Specify IN (ANY) to pivot on values in pivot-source-table. If ANY is specified, then the XML column is similar to the XML column generated for IN (ALL) but the NULL values for the aggregates are eliminated from the XML string. This form of the IN clause results in a more compact XML column.

A pivoted derived table with an IN (ANY) clause is similar to a derived table specified with IN(ALL) clause with the exception that NULL values are not included in the XML string of the extra column.

Specify the XML keyword when using this form of the IN clause.

Remarks

The definition of a pivoted derived table contains an input table expression, pivot-source-table. The columns and values to pivot on are defined in the FOR and IN clauses. The grouping columns of the pivoted derived tables are a subset of the columns of pivot-source-table. A pivoted derived table is computed by grouping pivot-source-table on the grouping columns and then computing the aggregate functions specified in the aggregate clause. The pivoted derived table has a column for each value of the grouping columns. There are extra columns added to the pivoted derived table, one for each pair of an item in the aggregate clause and an item in the IN clause. The values of the new columns are the aggregate functions specified in the aggregate clause. The names of these new columns are generated from the aliases specified in the aggregate clause for aggregate functions, and the aliases and values specified in the IN clause. In total, if A aggregate functions are specified, and the IN clause has I elements, then there are A x I extra columns. However, if the XML clause is specified, then only one extra column is added. This column contains the aggregates, in the XML format, for all of the combinations of the values in the IN clause and the aggregate clause, in one string.

Privileges

You must have SELECT privileges on the objects referenced in pivot-source-table, and in any subqueries (for example, IN subquery-expression). For the IN variable-name syntax, variable-name must be within the current scope of the statement and be set when the statement is described, open, or run.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example selects data from the Employees table and pivots it on the DepartmentID column where the Department ID is 100, 200, 300, 400, or 500.

SELECT * 
FROM ( SELECT DepartmentID, State, Salary 
       FROM   Employees
       WHERE State IN ( 'OR', 'CA', 'AZ', 'UT' )
     ) MyPivotSourceData
   PIVOT ( 
      SUM( Salary ) TotalSalary  
      FOR DepartmentID IN ( 100, 200, 300, 400, 500 )
   ) MyPivotedData
ORDER BY State;

STATE	100_TotalSalary	200_TotalSalary	300_TotalSalary	400_TotalSalary	500_TotalSalary
AZ	(NULL)	(NULL)	93,732.000	(NULL)	85,300.800
CA	(NULL)	156,600.000	(NULL)	(NULL)	(NULL)
OR	(NULL)	47,653.000	(NULL)	80,339.000	54,790.000
UT	306,318.690	37,900.000	31,200.000	107,129.000	59,479.000

In the results, the aggregate alias and the values for DepartmentID are included in the column names of the result set (for example, 100_TotalSalary) to clarify which value is being pivoted. The column names in this example mean "the total salary for department X". The salaries for employees in each State/DepartmentID tuple are aggregated (in this case, summed together).

The following is an example of the IN variable-name syntax when you have only one pivot column. The variable must be an array with elements of compatible domains with pivot-column:

CREATE VARIABLE @var INT ARRAY;
SET @var = ( SELECT ARRAY_AGG( DISTINCT  DepartmentID ORDER BY Salary ) FROM Employees ) 
SELECT *
FROM ( SELECT DepartmentID,State, Salary FROM Employees ) p
PIVOT  (
   	 SUM(Salary) S, COUNT(*) C
   	 FOR DepartmentID in @var
) PivotTable

The following is an example of the IN variable-name syntax when you have a set of pivot columns. The variable must be an array with rows whose elements have compatible domains with the columns in the FOR clause:

CREATE VARIABLE @var ROW( DeptID INT, St CHAR(16)) ARRAY;
SET @var = ( SELECT ARRAY_AGG( DISTINCT ROW( DepartmentID, State) ORDER BY Salary ) FROM Employees )
SELECT *
FROM ( SELECT DepartmentID,State, Salary FROM Employees ) p
PIVOT (
    SUM ( Salary ) S, COUNT(*) C
    FOR ( DepartmentID, State ) IN @var
) PivotTable

The following statements compute the same pivoted derived table using the different supported PIVOT constructs. Different PIVOT constructs offer alternative ways to generated desired pivoted derived tables where the IN clause is dynamically generated based on the current data.

PIVOT ( ... IN (constant-list))
You can use this form when the constants to be specified in the IN clause are known.
```
SELECT * 
   FROM ( SELECT DepartmentID, City FROM Employees) E
   PIVOT  ( 
     COUNT(*) AS C
     FOR DepartmentID IN ( 100, 200, 300 ) ) AS PivotedTable
 ORDER BY City;
```
City 100_C 200_C 300_C

Charlottetown 0 2 0

Cornwall 2 1 1

Elora 0 1 0

... ... ... ...

PIVOT ( ... IN (generated-constant-list))

A dynamically generated IN clause can be used to generate the desired IN clause based on the current data in the Employees table.

BEGIN
DECLARE stmt LONG VARCHAR;
 SET stmt =    ( SELECT 'SELECT * INTO dba.ConstantsTable
        FROM ( SELECT DepartmentID, City FROM Employees ) p 
        PIVOT  ( 
            COUNT(*) AS C
            FOR DepartmentID IN ( ' + ( SELECT LIST( DepartmentID ) 
     FROM ( SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID < 400 ) T )
   +  ' ) )  AS PivotedTable 
 ORDER BY City' );
EXECUTE IMMEDIATE stmt;
END;

After the statements finish, execute the following statement to view the content of the pivoted derived table, XMLTable, that you created:

SELECT * FROM XMLTable;

City	100_C	200_C	300_C
Charlottetown	0	2	0
Cornwall	2	1	1
Elora	0	1	0
...	...	...	...

PIVOT ( ... IN variable-name )

The IN clause can be specified using a variable of type ARRAY which can be set to the desired set of values.

BEGIN
DROP TABLE IF EXISTS VarTable;
DECLARE @var INT ARRAY;
SET @var = ( SELECT  ARRAY_AGG( DepartmentID )  FROM  ( SELECT DISTINCT DepartmentID FROM Employees WHERE DepartmentID < 400 ) T ) ;
SELECT * INTO dba.VarTable
        FROM (SELECT DepartmentID, City FROM Employees) E
        PIVOT  ( 
            COUNT(*) AS C
            FOR DepartmentID IN @var ) AS PivotedTable
ORDER BY City;
END

SELECT * from VarTable;

City	100_C	200_C	300_C
Charlottetown	0	2	0
Cornwall	2	1	1
Elora	0	1	0
...	...	...	...

PIVOT XML( ... IN ( subquery-expression) )

The following example generates a pivoted derived table in XML format, and then extracts the pivoted derived table from the data. You can use this form when the constants in the IN clause are not known.

BEGIN
DECLARE qry LONG VARCHAR;
DECLARE city_c INT;
DECLARE total_c INT;
CREATE OR REPLACE VARIABLE globalvals ARRAY OF ROW( City CHAR(26), DepartmentID INT, C INT );

SELECT COUNT(*) total_c, COUNT( DISTINCT city ) city_c, 
ARRAY_AGG( ROW ( City, DepartmentID, C ) ORDER BY city, DepartmentID )  INTO  total_c, city_c, globalvals
FROM (
SELECT PivotedTable.City, DepartmentID, C
FROM   ( SELECT * 
        FROM (SELECT DepartmentID, City FROM Employees) E
        PIVOT  XML ( 
            COUNT(*) AS C
            FOR DepartmentID IN ( SELECT DepartmentID FROM Employees WHERE DepartmentID < 400 ) )  AS EEE  ) AS PivotedTable,
LATERAL (SELECT * FROM  openxml( PivotedTable.[DepartmentID_xml], '/PivotSet/item') 
         WITH ( DepartmentID INT 'column[@name="DepartmentID"]',  C  INT 'column[@name="C"]' ) ) XXX
) AS  dt;

SELECT 
 'SELECT ( globalvals[[ row_num ]]).city AS City, 
 ' || LIST('(globalvals[[row_num+ ' || row_num || ']]).C AS ' || STRING( '[', globalvals[[row_num+1]].DepartmentID, '_C]') ) ||  '
 INTO dba.XMLTable FROM sa_rowgenerator(1, ' || total_c || ' , ' || (total_c/city_c) || ' )' INTO qry
FROM sa_rowgenerator( 0, (total_c/city_c) -1); 

DROP TABLE IF EXISTS XMLTable;
EXECUTE IMMEDIATE qry;
DROP VARIABLE IF EXISTS globalvals;

END;

After the example finishes, execute the following statement to view the content of the pivoted derived table, XMLTable, that you created:

SELECT * FROM XMLTable;

City	100_C	200_C	300_C
Charlottetown	0	2	0
Cornwall	2	1	1
Elora	0	1	0
...	...	...	...