Input
- Input table: pivot_input, as in Pivoting Example 1: Pivot Keys
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 optional. If omitted, the order of values can vary. The function always adds any NULL values at the end.
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 (value) in dense format in the output columns value_0, value_1, and value_2, which contain the temperature, pressure, and dewpoint, respectively.
| sn | city | week | value_0 | value_1 | value_2 |
|---|---|---|---|---|---|
| 1 | Asheville | 1 | 32 | 1020.8 | 27.6F |
| 2 | Asheville | 2 | 32 | 1021.3 | 27.4F |
| 3 | Asheville | 3 | 34 | 1021.7 | 28.2F |
| 4 | Nashville | 1 | 42 | 1021 | 29.4F |
| 5 | Nashville | 2 | 44 | 1019.8 | 29.2F |
| 6 | Brownsville | 2 | 47 | 1019 | 28.9F |
| 7 | Brownsville | 3 | 46 | 1019.2 | 28.9F |