1.0 - 8.00 - Pivoting Example 1: Pivot Keys - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

This example specifies the PivotColumn argument and, with the PivotKeys argument, the values from the pivot column to use as pivot keys. Because PivotKeys does not include 'dewpoint', the function ignores rows that include 'dewpoint'.

Input

The input table, pivot_input, contains temperature, pressure, and dewpoint data for three cities, in sparse format.

pivot_input
sn city week attribute value
1 Asheville 1 temp 32
1 Asheville 1 pressure 1020.8
1 Asheville 1 dewpoint 27.6F
2 Asheville 2 temp 32
2 Asheville 2 pressure 1021.3
2 Asheville 2 dewpoint 27.4F
3 Asheville 3 temp 34
3 Asheville 3 pressure 1021.7
3 Asheville 3 dewpoint 28.2F
4 Nashville 1 temp 42
4 Nashville 1 pressure 1021
4 Nashville 1 dewpoint 29.4F
5 Nashville 2 temp 44
5 Nashville 2 pressure 1019.8
5 Nashville 2 dewpoint 29.2F
6 Brownsville 2 temp 47
6 Brownsville 2 pressure 1019
6 Brownsville 2 dewpoint 28.9F
7 Brownsville 3 temp 46
7 Brownsville 3 pressure 1019.2
7 Brownsville 3 dewpoint 28.9F

SQL Call

SELECT * FROM Pivoting (
  ON pivot_input PARTITION BY sn, city, week ORDER BY week, attribute
  USING
  PartitionColumns ('sn', 'city', 'week')
  PivotKeys ('temp', 'pressure') 
  PivotColumn ('attribute') 
  TargetColumns ('value1')
) AS dt ORDER BY 1,2,3;
The ORDER BY clause is required. If omitted, the output table column content is nondeterministic (for more information, see Nondeterministic Results).

Output

The function outputs the input column contents in dense format in the output columns value_temp and value_pressure, which contain the temperature and pressure, respectively. Because these values are numeric, the function casts them to VARCHAR.

sn city week value_pressure value_temp
1 Asheville 1 1020.8 32
2 Asheville 2 1021.3 32
3 Asheville 3 1021.7 34
4 Nashville 1 1021 42
5 Nashville 2 1019.8 44
6 Brownsville 2 1019 47
7 Brownsville 3 1019.2 46