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

This example specifies the target columns by name and specifies the default values for the optional syntax elements. If you omit the optional syntax elements, the result is the same.

Input

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

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,4,5;

Output

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

 sn city        week attribute value_col 
 -- ----------- ---- --------- --------- 
  1 asheville      1 dewpoint  27.6f    
  1 asheville      1 pressure  1020.8   
  1 asheville      1 temp      32       
  2 asheville      2 dewpoint  27.4f    
  2 asheville      2 pressure  1021.3   
  2 asheville      2 temp      32       
  3 asheville      3 dewpoint  28.2f    
  3 asheville      3 pressure  1021.7   
  3 asheville      3 temp      33       
  4 nashville      1 dewpoint  29.4f    
  4 nashville      1 pressure  1021.0   
  4 nashville      1 temp      42       
  5 nashville      2 dewpoint  29.2f    
  5 nashville      2 pressure  1019.8   
  5 nashville      2 temp      44       
  6 brownsville    2 dewpoint  28.9f    
  6 brownsville    2 pressure  1019.0   
  6 brownsville    2 temp      46       
  7 brownsville    3 dewpoint  28.9f    
  7 brownsville    3 pressure  1019.2   
  7 brownsville    3 temp      46

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