As indicated in the syntax, specify at least one Aggregate function with the PIVOT operator.
Columns with CLOB, BLOB, UDT, XML, or JSON data types are not allowed with the PIVOT operator.
Column names are not allowed within the IN-list. Only values or expressions (arithmetic expressions such as MOD or ABS, or string Manipulation expressions such as LENGTH, REVERSE) are allowed.
Measure columns and pivot columns of the PIVOT operator are not allowed in the assign list of the SELECT statement.
If n number of Aggregate functions are specified where n is greater than 1, then the alias name must be specified for at least (n-1) aggregate functions.
The cname specified in the derived_table_name takes precedence over the alias names derived from the IN-list.
If the alias names are not specified for the column values listed in the IN clause, the database processing encloses the column values into double quotes and converts these string literals to alias names using the default format. The alias names are used as column names of the pivoted table.
If the length of the alias name derived from a column value exceeds the alias name limit of 128 characters (if EON feature is enabled) or 30 characters (if EON is not enabled), the alias name is truncated.
If the IN-list contains case-specific values such as ‘abc’ & ‘ABC’, the values are treated the same and an error occurs.
PIVOT supports the UNPIVOT or TD_UNPIVOT functions as a query source for the PIVOT operator.
The PIVOT/UNPIVOT operator uses a single dimensional way of converting rows to columns, or columns to rows. You can swap both rows and columns within a single query (for example, using UNPIVOT as source to PIVOT). This provides flexibility when using the two-dimensional method of interchanging data in a table.
Using the DT column list for UNPIVOT as a query source to PIVOT is optional.
- Specifiying at least one aggregate function with the WITH operator is mandatory.
- SUM, AVG, MIN, and MAX aggregate functions are supported.
- The cname specified in the derived_table_name takes precedence over the alias names derived for the aggregated result columns.
- DISTINCT keyword is not supported with aggregate column.
- Column list is not allowed if an asterisk (*) is specified.
- Aggregating a column list or * may produce meaningless results if the values aggregated are not related. For example, if some pivot columns are for SUM and some are for an AVG, WITH SUM(*) is not a meaningful value.
- Column names mentioned in the aggregate function should be PIVOT columns or subset of PIVOT columns.
- Alias names are not allowed in the IN-list.
- Alias names in the PIVOT derived table are not allowed.
- The SELECT list of the subquery must contain only one column reference.
- The subquery must return at least one row.
- The results returned by the subquery cannot exceed 32KB, and the row count must be less than or equal to 16.
- SET operations are not allowed on a PIVOT query that has a subquery in the IN-list.
- Columns generated by an IN-list subquery cannot be explicitly used in the SELECT.
- You cannot use a subquery in a PIVOT IN-list with DDL statements or multistatement requests.
- A PIVOT query cannot include both a WITH clause and a subquery in the IN-list.
For examples of wide tables, see Pivot Examples.