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

Teradata Vantageā„¢ NewSQL Engine Release Summary

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Release Notes
Publication ID
B035-1098-162K
Language
English (United States)
Last Update
2019-05-03

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.

Benefits

  • 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.

Considerations

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.