16.20 - Subquery Support in PIVOT IN-List - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ NewSQL Engine Release Summary

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Release Notes
Publication ID
English (United States)
Last Update

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 now issue the query as a subquery in PIVOT IN-list. This enhancement supports subqueries that return multiple values, as opposed to returning a single value.


  • Allows you to use a subquery instead of providing long lists of values in the PIVOT IN-list.
  • Allows you to use the subquery result for your PIVOT operation.


PIVOT with a subquery in the IN-list is not supported in a multistatement request. PIVOT columns are decided dynamically at the optimization phase. Because of this dynamic behavior, the following are usage considerations of a PIVOT query with a subquery in the IN-list.

  • Not supported in DDL creation statements.
  • Not supported in stored procedure's cursor FETCH statement.
  • SET operations are not allowed on a PIVOT query if subquery is given in the IN-list.
  • Resultant PIVOT column names cannot be explicitly specified in the SELECT list.
  • Does not support ORDER BY clause.

Additional Information

For more information, see PIVOT in Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates, B035-1145.