Pivoting Example: PivotKeys | Teradata Vantage - Pivoting Example: PivotKeys - Teradata Vantage

Input

• Input table: pivot_input, as in Pivoting Example: NumberOfRows

SQL Call

SELECT * FROM Pivoting (
  ON pivot_input PARTITION BY sn,city,week
  USING
  PartitionColumns ('sn','city', 'week')
  PivotKeys ('temp','pressure')
  PivotColumn ('attribute')
  TargetColumns ('value1')
) AS dt ORDER BY 1,2,3;

With PivotKeys, the function does not use the ORDER BY clause.

Output

To create the output table, the function pivots the input table on the partition columns (sn, city, and week) and outputs the contents of the target column (value1) in dense format in the output columns value1_pressure and value1_temp.

         sn  city          week  value1_pressure  value1_temp
-----------  ------------------  ---------------- ------------------------------
          1  asheville        1  1020.8           32
          2  asheville        2  1021.3           32
          3  asheville        3  1021.7           34
          4  nashville        1  1021             42
          5  nashville        2  1019.8           44
          6  brownsville      2  1019             47
          7  brownsville      3  1019.2           46
          8  beattyville      3  null             null

Download a zip file of all examples and a SQL script file that creates their input tables.