Pivot function in Teradata with examples

PIVOT in Teradata

Pivot function is used for transforming rows into columns in Teradata. It allows us to aggregate and rotate data to create easy-to-read tables that is used for reporting purposes.

To create the pivot table, we need to specify the PIVOT operator in the FROM clause of SELECT statement. Along with PIVOT operator, we can specify other clauses as well with the SELECT query.

Syntax for PIVOT function

Syntax Elements

  • aggr_fn – An aggregate function that supports a single argument. Example, SUM,COUNT,AVG ..etc
  • pvt_aggr_alias – An alias name specified for the aggregate function.
  • expr – An expression or column value.
  • expr_alias_name – An alias name specified for the values/expressions specified in the IN list.
  • derived_table_name – The table name specified for the resultant pivoted table.

PIVOT example in Teradata

Lets create the Quartely_Results table and use the Pivot function to transpose the rows to columns in Teradata.

Pivot Example in Teradata
Pivot Example in Teradata

In this table, we can calculate the Sum of Sales and Cogs (Cost of Good Sold) per Quarter using Pivot function as below. The IN list contains the alias name such as Quarter1,Quarter2 & Quarter3. Those alias names are concatenated with the alias name(ss1 &qtr) specified by the aggregate functions to build the column names of the output pivoted table.

Output of Pivoted table

Results of Pivot function in Teradata
Results of Pivot function in Teradata

Without using PIVOT function, We can fetch the same results using the SELECT and CASE statements as below.

Example : Naming columns with the <column_value_list> Values

If we did not specify the column names for pivot table explicitly, the names of the columns are built internally by adding the aggregated column name to the values as below.

This is re-written as an the equivalent SELECT query with CASE statement

Output of Pivoted table with internally build column names

Naming columns with column values in Pivot table
Naming columns with column values in Pivot table

Usage Notes for Pivot function

  • Specify one Aggregate function with the PIVOT operator.
  • Columns with CLOB, BLOB, UDT, XML, or JSON data types are not allowed with the PIVOT operator.
  • If the IN-list contains case-specific values such as ‘abc’ & ‘ABC’, the values are treated the same and an error occurs.