1.0 - 8.00 - Unpivoting Example 2: Specified Target Columns, Specified 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 nondefault values for the optional arguments.

SQL Call

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

Output

Because InputTypes has the value 'true', the output table has a separate value column for each target column. The target columns temp, pressure, and dewpoint have the data types INTEGER, DOUBLE PRECISION, and VARCHAR (respectively); therefore, their corresponding unpivoted columns have the data types LONG, DOUBLE PRECISION, and VARCHAR.

sn city week climate_attributes attributevalue_long attributevalue_double attributevalue_str
1 Asheville 1 temp 32    
1 Asheville 1 pressure   1020.79998779297  
1 Asheville 1 dewpoint     27.6F
2 Asheville 2 temp 32    
2 Asheville 2 pressure   1021.29998779297  
2 Asheville 2 dewpoint     27.4F
3 Asheville 3 temp 34    
3 Asheville 3 pressure   1021.70001220703  
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.79998779297  
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.20001220703  
7 Brownsville 3 dewpoint     28.9F