Pivoting Example: NumberOfRows | Teradata Vantage - Pivoting Example: NumberOfRows - Teradata Vantage

This example specifies the NumberOfRows syntax element.

Input

The input table, pivot_input, contains temperature, pressure, and dewpoint data for four cities, in sparse format.

 sn |    city     | week | attribute | value
----+-------------+------+-----------+--------
  1 | Asheville   |    1 | temp      | 32
  1 | Asheville   |    1 | pressure  | 1020.8
  1 | Asheville   |    1 | dewpoint  | 27.6F
  2 | Asheville   |    2 | temp      | 32
  2 | Asheville   |    2 | pressure  | 1021.3
  2 | Asheville   |    2 | dewpoint  | 27.4F
  3 | Asheville   |    3 | temp      | 34
  3 | Asheville   |    3 | pressure  | 1021.7
  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.8
  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.2
  7 | Brownsville |    3 | dewpoint  | 28.9F
  8 | Beattyville |    3 | temp      | null
  8 | Beattyville |    3 | pressure  | null
  8 | Beattyville |    3 | dewpoint  | null

SQL Call

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

The ORDER BY clause is required. If omitted, the output table column content is nondeterministic (for more information, see Nondeterministic Results and UniqueID Syntax Element). The function adds any NULL values at the end.

Output

The function outputs the input column contents in dense format in the output columns value1_0, value1_1, and value1_2, which contain the dewpoint, pressure, and temperature, respectively. Because these values are numeric, the function casts them to VARCHAR.

         sn  city         week  value1_0  value1_1  value1_2
-----------  -----------------  ------------------- ----------
          1  asheville       1  27.6f     1020.8    32
          2  asheville       2  27.4f     1021.3    32
          3  asheville       3  28.2f     1021.7    34
          4  nashville       1  29.4f     1021      42
          5  nashville       2  29.2f     1019.8    44
          6  brownsville     2  28.9f     1019      47
          7  brownsville     3  28.9f     1019.2    46
          8  beattyville     3  null      null      null

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