Usage Notes - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantage™
For the PIVOT operation, column names within the Aggregate functions are referred to as measure columns, and column names in the FOR clause are referred to as pivot columns.

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.

Using the DT column list for UNPIVOT as a query source to PIVOT is optional.

If the WITH clause is specified in the PIVOT query:
  • 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.
To avoid the overhead of issuing a separate query to generate values for input to the PIVOT IN-list clause as hard coded constants, you can issue the query as a subquery in the PIVOT IN-list. If a PIVOT query has a subquery in the IN-list:
  • 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.