dynamic_pivot name: col storable, value: expression, [group_by(...)]?

The dynamic_pivot verb packs runtime keys into one object column named pivot_data.

Use it when the set of keys is not known while you write the query. pivot names every output column at compile time. dynamic_pivot reads the keys from the data and stores them in pivot_data. pivot_data is the object column whose keys are shown as virtual columns. The verb casts name to string, and that string is the key. Values of name that cast to the same string share one key. A null name is omitted from the object. Each value in pivot_data is a variant.

On an event dataset the result is one row per (group_by key, timestamp). On a table dataset the result is one row per group_by key, and no timestamp is added. dynamic_pivot does not accept options(align:…).

The arguments are as follows:

• name: Column reference. The verb casts it to string, and those strings become the keys of pivot_data
• value: Expression stored under each key. Rows that share a group and the same string key are reduced with any_not_null, which keeps one non-null value when several rows disagree
• group_by: Optional. When you omit it, or pass an empty group_by(), the grouping is every column not referenced in name or value, excluding the valid-from and valid-to columns

Additional details:

• The number of keys is capped. The default cap is 100. The cap is one budget for the whole query, ordered by the string key, and keys past the cap are dropped. A group that loses every key still produces a row
• Sorting a path inside pivot_data casts that path to the type of value
• The name and value arguments cannot contain an aggregation function
• dynamic_pivot accepts event and table inputs. It is rejected for interval and resource datasets

Aggregates

dynamic_pivot packs the object. Put the aggregate on statsby or timechart. An aggregation function cannot appear in name or value.

One row per group key. A label built with case or if has to be a column before the aggregate. statsby with no time column returns a table, and dynamic_pivot on a table is one row per group_by key.

make_col caller:case(contains(agent, "Mobile"), "mobile", true, "browser")
statsby requests: count(), group_by(caller, status)
dynamic_pivot status, requests, group_by(caller)

make_col builds the caller label. statsby writes the aggregate to requests and returns one row per (caller, status). dynamic_pivot packs each status into pivot_data and keeps one row per caller.

• value is the column that aggregate wrote, here requests. Any aggregation function belongs in statsby. To pack a sum, write sum(amount) there and pass that column as value.
• name is a column reference. Here it is status. Build a derived label with make_col first, then pass that column.
• group_by is the column that stays on the row. Here it is caller.
• statsby must already emit one row per (caller, status). dynamic_pivot reduces a duplicate key with any_not_null.

One row per time bin. Put the span and the aggregate on timechart. timechart returns intervals, and dynamic_pivot rejects intervals, so make_event is required. Group by the bin start. make_event leaves _c_valid_to and _c_bucket in place, and the default grouping would keep both.

make_col outcome:case(int64(status) >= 400, "Error", int64(status) < 400, "Success", true, "Unknown")
timechart 1m, requests: count(), group_by(outcome)
make_event
dynamic_pivot outcome, requests, group_by(_c_valid_from)

int64(status) returns null when status is null or not a number, such as "-" or "N/A". Both comparisons are then null, so neither matches, and the true branch labels the row "Unknown". name is the column outcome, and value is requests. List any other column to keep, such as a second timechart group, in that group_by.

Use pivot with options(align:…) when every series name is known while you write the query.

Categories

• Aggregate

Accelerable

dynamic_pivot is never accelerable. A dataset that only uses accelerable verbs can be accelerated, making
queries on the dataset respond faster.

Examples

dynamic_pivot metric_name, metric_value, group_by(service)

For each service and event timestamp, packs metric_name and metric_value into pivot_data. pivot_data is the object column whose keys are shown as virtual columns. The keys are metric_name cast to string, and the value for each key is one non-null metric_value kept by any_not_null, stored as a variant. Use this when the set of metric names is not known ahead of time, so it cannot be listed as explicit pivot columns. The output is one row per (service, timestamp).

make_col caller:case(contains(agent, "Mobile"), "mobile", true, "browser")
statsby requests: count(), group_by(caller, status)
dynamic_pivot status, requests, group_by(caller)

make_col builds the caller label before the aggregate. statsby returns a table with one row per (caller, status) and the count in requests. dynamic_pivot then packs status into pivot_data and keeps one row per caller. name is the column status. The aggregate stays on statsby, because value cannot contain an aggregation function. dynamic_pivot reduces a duplicate (caller, status) with any_not_null.

make_col outcome:case(int64(status) >= 400, "Error", int64(status) < 400, "Success", true, "Unknown")
timechart 1m, requests: count(), group_by(outcome)
make_event
dynamic_pivot outcome, requests, group_by(_c_valid_from)

timechart bins the window and counts each outcome. It returns intervals, which dynamic_pivot rejects, so make_event turns each bin into an event. Group by _c_valid_from so the bin start is the row key and make_event does not keep _c_valid_to or _c_bucket as groups. name is the column outcome. The count stays on timechart. int64(status) returns null when status is null or not a number, such as "-" or "N/A". Both comparisons are then null, so the true branch labels those rows "Unknown".