pivot [options(...)]?, name: col storable, value: col storable, [name_value: expression]+, [group_by(...)]?

The pivot verb rotates a table by transforming rows into columns.

This verb can be used to transform a narrow table (e.g. timestamp, year, product, sales) into a wider table (e.g. timestamp, year, laptop_sales, tablet_sales, smartphone_sales).

The arguments are as follows:

• options(align:x): Optional argument to specify alignment, which denotes the period for aggregating nearby data points together onto a time grid
• name: Expression used to select rows for generating the name_value rows
• value: Expression used to populate the name_value columns
• name_value_0, ..., name_value_n: Columns to generate. These arguments can be specified either using the name:expression syntax or by just providing an expression. If name:expression syntax is used, the name of the generated column is set to name. If only expression is passed, pivot will try to auto-generate a name. The rows of each name_value column is set to:
  • Any non-null result of evaluating the value argument, if the value of name argument equals name_value and no alignment is specified
  • Last non-null result of evaluating the value argument, if the value of name argument equals name_value and an alignment is specified using options(align:x)
  • Null, otherwise
• group_by: Optional grouping. Default grouping is all columns not referenced in the preceding arguments. When no alignment is specified, there is also an implicit grouping on the timestamp column, similar to how the timestats verb treats timestamp columns.

Additional details:

• None of the arguments can contain an aggregation function
• pivot is not supported for interval and resource datasets
• order_by specification is not allowed

Options

Categories

• Aggregate

Accelerable

pivot is sometimes accelerable, depending on options used. A dataset that only uses accelerable verbs can be accelerated, making queries on the dataset respond faster.

Examples

pivot product, sales, laptop_sales:"laptop", tablet_sales:"tablet", smartphone_sales:"smartphone", group_by(year)

Consider the following input table A:

The above statement transforms table A to:

pivot product, sales, "laptop", "tablet", "smartphone", group_by(year)

The above statement transforms table A to:

pivot in this case auto-generates the column names.

pivot product, sales, "laptop", "tablet", "smartphone"

The above statement transforms table A to:

The group_by optional and in this case is inferred to be group_by(year).

pivot concat_strings(product, "item"), sales * 2, "laptop_item", "tablet_item", "smartphone_item", group_by(year)

The above statement transforms table A to:

pivot accepts any scalar expression not containing an aggregate function as an argument.

pivot product, sales, "laptop", "tablet", "smartphone", group_by(year)

Consider the following input table B:

The result of the above statement for this input is non-deterministic because two groups (namely "laptop" for the year 2021 and "tablet" for the year 2022) contains multiple non-null value results. In such cases, pivot will pick any non-null result.

One possible output is:

Again, consider another example, input table C:

The result of the above statement for this input, however, is not non-deterministic, despite the timestamp column having conflicting values per group. This is because the timestamp column is treated specially: it is implicitly used as a grouping column, similar to how the timestats verb treats timestamp columns.

The output is:

For inputs with conflicting timestamps, it is recommended that you use the options(align:x) argument, as demonstrated in the next example.

pivot options(align:3m), product, sales, "laptop", "tablet", "smartphone", group_by(year)

The above statement, for the window 10/31/2023 15:00:00 to 10/31/2023 17:00:00, transforms table C to: