pivot plugin
Rotates a table by turning the unique values from one column in the input table into multiple columns in the output table, and performs aggregations where they are required on any remaining column values that are wanted in the final output.
T | evaluate pivot(PivotColumn)
Note
The output schema of the pivot plugin is based on the data and therefore query may produce different schema for any two runs. This also means that query that is referencing unpacked columns may become 'broken' at any time. Due to this reason - it is not advised to use this plugin for automation jobs.
Syntax
T | evaluate pivot(pivotColumn[, aggregationFunction] [,column1 [,column2 ... ]])
Arguments
- pivotColumn: The column to rotate. each unique value from this column will be a column in the output table.
- aggregation function: (optional) aggregates multiple rows in the input table to a single row in the output table. Currently supported functions:
min(),max(),take_any(),sum(),dcount(),avg(),stdev(),variance(),make_list(),make_bag(),make_set(),count()(default iscount()). - column1, column2, ...: (optional) column names. The output table will contain an additional column per each specified column. default: all columns other than the pivoted column and the aggregation column.
Returns
Pivot returns the rotated table with specified columns (column1, column2, ...) plus all unique values of the pivot columns. Each cell for the pivoted columns will contain the aggregate function computation.
Examples
Pivot by a column
For each EventType and States starting with 'AL', count the number of events of this type in this state.
StormEvents
| project State, EventType
| where State startswith "AL"
| where EventType has "Wind"
| evaluate pivot(State)
| EventType | ALABAMA | ALASKA |
|---|---|---|
| Thunderstorm Wind | 352 | 1 |
| High Wind | 0 | 95 |
| Extreme Cold/Wind Chill | 0 | 10 |
| Strong Wind | 22 | 0 |
Pivot by a column with aggregation function
For each EventType and States starting with 'AR', display the total number of direct deaths.
StormEvents
| where State startswith "AR"
| project State, EventType, DeathsDirect
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect))
| EventType | ARKANSAS | ARIZONA |
|---|---|---|
| Heavy Rain | 1 | 0 |
| Thunderstorm Wind | 1 | 0 |
| Lightning | 0 | 1 |
| Flash Flood | 0 | 6 |
| Strong Wind | 1 | 0 |
| Heat | 3 | 0 |
Pivot by a column with aggregation function and a single additional column
Result is identical to previous example.
StormEvents
| where State startswith "AR"
| project State, EventType, DeathsDirect
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect), EventType)
| EventType | ARKANSAS | ARIZONA |
|---|---|---|
| Heavy Rain | 1 | 0 |
| Thunderstorm Wind | 1 | 0 |
| Lightning | 0 | 1 |
| Flash Flood | 0 | 6 |
| Strong Wind | 1 | 0 |
| Heat | 3 | 0 |
Specify the pivoted column, aggregation function and multiple additional columns
For each event type, source and state, sum the number of direct deaths.
StormEvents
| where State startswith "AR"
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect), EventType, Source)
| EventType | Source | ARKANSAS | ARIZONA |
|---|---|---|---|
| Heavy Rain | Emergency Manager | 1 | 0 |
| Thunderstorm Wind | Emergency Manager | 1 | 0 |
| Lightning | Newspaper | 0 | 1 |
| Flash Flood | Trained Spotter | 0 | 2 |
| Flash Flood | Broadcast Media | 0 | 3 |
| Flash Flood | Newspaper | 0 | 1 |
| Strong Wind | Law Enforcement | 1 | 0 |
| Heat | Newspaper | 3 | 0 |