This example specifies the PivotColumn argument and, with the PivotKeys argument, the values from the pivot column to use as pivot keys. Because PivotKeys does not include 'dewpoint', the function ignores rows that include 'dewpoint'.
Input
The input table, pivot_input, contains temperature, pressure, and dewpoint data for three 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 |
SQL Call
SELECT * FROM Pivoting ( ON pivot_input PARTITION BY sn, city, week ORDER BY week, attribute USING PartitionColumns ('sn', 'city', 'week') PivotKeys ('temp', 'pressure') PivotColumn ('attribute') 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).
Output
The function outputs the input column contents in dense format in the output columns value_temp and value_pressure, which contain the temperature and pressure, respectively. Because these values are numeric, the function casts them to VARCHAR.
sn | city | week | value_pressure | value_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 |