Optional Syntax Elements for TD_Pivoting - Analytics Database

Database Analytic Functions
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantage™
Accumulate
Specifies the copied columns to the output columns. Accumulate Column values from the last row of the partition will be copied to the output.
RowsPerPartition
Use RowsPerPartition when no column contains pivot keys, but you can specify a maximum number of rows in any partition. The function pivots the input rows into this number of columns in the output table. If a partition has fewer than rows_per_partition rows, the function adds NULL values; if a partition has more than rows_per_partition rows, the function omits the extra rows. If you use this argument, you must use the ORDER BY clause to order the input data; otherwise, the contents of the output table columns may vary from run to run. MinValue = 1 MaxValue = 2047, if aggregation is not given If this argument is used with aggregation, then the aggregation will consider rows_per_partition rows for aggregation of the data. MaxValue =INT_MAX, if aggregation is given. Note: RowsPerPartition cannot be used with PivotColumn.
Required if you omit both PivotColumn and Aggregation, otherwise ignored.
PivotColumn
Specifies the input column name that contains the pivot keys.

PivotColumn cannot be used with RowsPerPartition.

Required if you omit both RowsPerPartition and Aggregation, otherwise ignored.
PivotKeys
Specifies the pivot_column values to use as pivot keys. The function ignores rows that contain other pivot_column values.
Required if you specify PivotColumn, otherwise ignored.
PivotKeysAlias
Specifies exactly one pivot key alias for each pivot_key. The nth pivot_key_alias_value applies to the nth pivot_key.
Optional with PivotKeys, otherwise not required.
DefaultPivotValues
Specifies exactly one default value for each pivot_key.
The nth default_pivot_value applies to the nth pivot_key. Each default_pivot_value data type must be compatible with the target_column data type.
Optional with PivotKeys, otherwise not required.
Aggregation
Specifies the aggregation for the target columns.
You can provide the Aggregation as one of the single value {CONCAT | UNIQUE_CONCAT | SUM | MIN | MAX | AVG} which applies to all target columns or you can specify multiple values for multiple target columns with the following format:
'ColumnName:{CONCAT|UNIQUE_CONCAT|SUM|MIN|MAX|AVG}' [,...]
Allows only 1 aggregation for 1 target column.
Required if you omit both RowsPerPartition and PivotColumn, otherwise ignored.
Default: No aggregation is done only one of the values is picked up for the output.
Delimiters
Specifies the delimiter to use for concatenating the target column values.
A delimiter is a single character string. You can specify a single delimiter values which will be applicable to all target columns or you can specify multiple delimiter values for multiple target columns with the following format:
'ColumnName:single_char' [,...]
Optional with CONCAT and UNIQUE_CONCAT Aggregation, otherwise not required.
Default: comma.
CombinedColumnSizes
Specifies the concatenated string maximum size.
You can specify a single values which will be applicable to all target columns or you can specify multiple size value for multiple target columns with the following format:
'ColumnName:size_value' [,...]
Default value of 'size' is 64000.
Maximum value of 'size' can be : 2097088000.
Consider the following:
  • If size <= 64000, VARCHAR datatype is generated for concatenated string in the output.
  • If size > 64000, CLOB datatype is generated for concatenated string in the output.
Considering that CLOB operations are relatively expensive, the function always generates a VARCHAR column for concatenated string in the output, unless user specifies a size > 64000 in the query. Therefore, it is mandatory to specify a size > 64000 in the query, for generating CLOB column for concatenated string in the output.
Optional with CONCAT and UNIQUE_CONCAT Aggregation, otherwise not required.
Default: 64000.
TruncateColumns
Specifies the columns from the target columns for which you want to truncate the concatenated string, if it is longer than the specified size.
Truncate columns must be part of the target columns.
Optional with CONCAT and UNIQUE_CONCAT Aggregation, otherwise not required.
Default: Nothing will be truncated.
OutputColumnNames
Specifies the column name to use for the output column.
The nth column name value applies to the nth output column.