[Feature] Pivot aggregations

[jaychia]

Summary

Pivots are aggregations where the distinct values of a column are provided as new columns.

This is difficult to implement in our current execution model as the resulting schema is not possible to predict at query construction-time.

As a workaround, we can implement a pivot where columns are provided instead of computed from distinct values.

Proposal

df
# +----------+----------+----------+
# | Question | Score    | Class    |
# +----------+----------+----------+
# | 1        | 10       | A        |
# | 1        | 20       | B        |
# | 2        | 20       | A        |
# | 2        | 30       | A        |
# | 3        | 30       | B        |
# | 3        | 40       | A        |
# +----------+----------+----------+

df.pivot("Question", columns=["1", "2", "3"]).agg([
    (df["Score"], "mean"),
])

# +----------+----------+----------+
# | 1_Score  | 2_Score  | 3_Score  |
# +----------+----------+----------+
# | 15       | 25       | 35       |
# +----------+----------+----------+

df.groupby("Class").pivot("Question", columns=["1", "2", "3"]).agg([
    (df["Score"], "mean"),
])

# +----------+----------+----------+----------+
# | Class    | 1_Score  | 2_Score  | 3_Score  |
# +----------+----------+----------+----------+
# | A        | 10       | 25       | 40       |
# | B        | 20       | Null     | 30       |
# +----------+----------+----------+----------+