Pivot

Supported in: Batch

Performs the specified aggregations on the input dataset grouped by a set of columns. Unique values to pivot on must be provided such that the output schema is known ahead of runtime. This improves runtime stability over time.

Transform categories: Aggregate, Popular

Declared arguments

• Aggregations - List of aggregations to perform on the dataset.
  List<Expression<AnyType>>
• Dataset - Dataset to perform aggregate on.
  Table
• Group by columns - List of columns to group the dataset by when aggregating.
  List<Column<AnyType>>
• Pivot by column - Column to pivot on.
  Column<T>
• Pivot by values - List of unique values used to pivot and aliases for the output. Alias values are used to construct the output column name according to the prefix / suffix argument.
  List<Tuple<Literal<T>, Literal<String>>>
• optional Prefix or suffix alias - If prefix, the output column name will be 'alias''aggregate', if suffix it will be 'aggregate'alias.
  Enum<Prefix, Suffix>

Type variable bounds: T accepts Boolean | Byte | Integer | Long | Short | String

Examples

Example 1: Base case

Argument values:

• Aggregations: [
  alias(
   alias: miles,
   expression:
  mean(
   expression: miles,
  ),
  )]
• Dataset: ri.foundry.main.dataset.a
• Group by columns: [airline]
• Pivot by column: airport
• Pivot by values: [(JFK, new_york), (LHR, london)]
• Prefix or suffix alias: null

Input:

Output:

---

Example 2: Base case

Argument values:

• Aggregations: [
  alias(
   alias: miles,
   expression:
  mean(
   expression: miles,
  ),
  )]
• Dataset: ri.foundry.main.dataset.a
• Group by columns: [airline]
• Pivot by column: airport
• Pivot by values: [(JFK, new_york), (LHR, london)]
• Prefix or suffix alias: SUFFIX

Input:

Output:

---