This example specifies the target columns by name and specifies nondefault values for the optional syntax elements.
Input
- Input table: unpivot_input, as in Unpivoting Example: Specified Target Columns, Default Optional Values
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.