1.1 - 8.10 - Unpivoting Example: Specified Target Columns, Specified Optional Values - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

This example specifies the target columns by name and specifies nondefault values for the optional syntax elements.

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                  NULL NULL              
  1 asheville      1 pressure                          NULL                1020.8 NULL              
  1 asheville      1 dewpoint                          NULL                  NULL 27.6f             
  2 asheville      2 pressure                          NULL                1021.3 NULL              
  2 asheville      2 dewpoint                          NULL                  NULL 27.4f             
  2 asheville      2 temp                                32                  NULL NULL              
  3 asheville      3 dewpoint                          NULL                  NULL 28.2f             
  3 asheville      3 pressure                          NULL                1021.7 NULL              
  3 asheville      3 temp                                33                  NULL NULL              
  4 nashville      1 dewpoint                          NULL                  NULL 29.4f             
  4 nashville      1 pressure                          NULL                1021.0 NULL              
  4 nashville      1 temp                                42                  NULL NULL              
  5 nashville      2 temp                                44                  NULL NULL              
  5 nashville      2 dewpoint                          NULL                  NULL 29.2f             
  5 nashville      2 pressure                          NULL                1019.8 NULL              
  6 brownsville    2 dewpoint                          NULL                  NULL 28.9f             
  6 brownsville    2 pressure                          NULL                1019.0 NULL              
  6 brownsville    2 temp                                46                  NULL NULL              
  7 brownsville    3 dewpoint                          NULL                  NULL 28.9f             
  7 brownsville    3 pressure                          NULL                1019.2 NULL              
  7 brownsville    3 temp                                46                  NULL NULL

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.