If you do not specify the PivotColumn and PivotKeys arguments, then you must order the input data; otherwise, the Pivot function output is nondeterministic with respect to the contents of the columns in the output table.
For example, suppose that you want to pivot this table:
Pivot Input Table input_table_1
| Id |
val |
| A |
x |
| A |
y |
| B |
w |
| B |
z |
SELECT * FROM pivot (
ON (SELECT id, val FROM input_table_1) PARTITION BY id
PartitionColumns ('id')
NumberOfRows (3)
TargetColumns ('val')
);
Each time you make the preceding call, the output table can be any of the following:
Possible Pivot Output Table 1
| Id |
val_0 |
val_1 |
val_2 |
| A |
x |
y |
|
| B |
w |
z |
|
Possible Pivot Output Table 2
| Id |
val_0 |
val_1 |
| A |
y |
x |
| B |
w |
z |
Possible Pivot Output Table 3
| Id |
val_0 |
val_1 |
| A |
x |
y |
| B |
z |
w |
Possible Pivot Output Table 4
| Id |
val_0 |
val_1 |
| A |
y |
x |
| B |
z |
w |
Now suppose that you want to pivot this table:
Pivot Input Table input_table_2
| Id |
val |
sequencenum |
| A |
x |
4 |
| A |
y |
2 |
| B |
w |
9 |
| B |
z |
3 |
When you call Pivot, you order the input by sequencenum:
SELECT * FROM pivot (
ON (SELECT id, val FROM input_table_2 ORDER BY sequencenum)
PARTITION BY id
PartitionColumns ('id')
NumberOfRows (3)
TargetColumns ('val')
);
Every time you use the preceding call, you get this result:
Pivot Output Table for Ordered Input Data
| Id |
val_0 |
val_1 |
| A |
y |
x |
| B |
z |
w |