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.