1.0 - 8.00 - Unpivoting Example 1: Specified Target Columns, Default Optional Values - 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 target columns by name and specifies the default values for the optional arguments. If you omit the optional arguments, the result is the same.

Input

The input table, unpivot_input, contains temperature, pressure, and dewpoint data for three cities, in dense (pivoted) format.

unpivot_input
sn city week temp pressure dewpoint
1 Asheville 1 32 1020.8 27.6F
2 Asheville 2 32 1021.3 27.4F
3 Asheville 3 34 1021.7 28.2F
4 Nashville 1 42 1021 29.4F
5 Nashville 2 44 1019.8 29.2F
6 Brownsville 2 47 1019 28.9F
7 Brownsville 3 46 1019.2 28.9F
Input Column Data Types
Column Data Type
sn INTEGER
city VARCHAR
week INTEGER
temp INTEGER
pressure DOUBLE PRECISION
dewpoint VARCHAR

SQL Call

SELECT * FROM Unpivoting (
  ON unpivot_input
  USING
  TargetColumns ('temp', 'pressure', 'dewpoint')
  AttributeColumn ('attribute')
  ValueColumn ('value_col')
  InputTypes ('false')
  Accumulate ('sn', 'city', 'week')
) AS dt ORDER BY 1, 2, 3;

Output

Because InputTypes has the value 'false', the value column has the data type VARCHAR.

sn city week attribute value_col
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.0
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.0
6 Brownsville 2 dewpoint 28.9F
7 Brownsville 3 temp 46
7 Brownsville 3 pressure 1019.2
7 Brownsville 3 dewpoint 28.9F