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 as required on any remaining column values that will appear in the final output.

Syntax

T | evaluate pivot(pivotColumn[, aggregationFunction] [,column1 [,column2 ... ]]) [: OutputSchema]

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
pivotColumn	string	✔️	The column to rotate. Each unique value from this column will be a column in the output table.
aggregationFunction	string		An aggregation function used to aggregate 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(). The default is count().
column1, column2, ...	string		A column name or comma-separated list of column names. The output table will contain an additional column per each specified column. The default is all columns other than the pivoted column and the aggregation column.
OutputSchema			The names and types for the expected columns of the pivot plugin output. Syntax: ( ColumnName : ColumnType [, ...] ) Specifying the expected schema optimizes query execution by not having to first run the actual query to explore the schema. An error is raised if the run-time schema doesn't match the OutputSchema schema.

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 State 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)

Output

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 State 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))

Output

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)

Output

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)

Output

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

Pivot with a query-defined output schema

The following example selects specific columns in the StormEvents table. It uses an explicit schema definition that allows various optimizations to be evaluated before running the actual query.

StormEvents
| project State, EventType
| where EventType has "Wind"
| evaluate pivot(State): (EventType:string, ALABAMA:long, ALASKA:long)

Output

EventType	ALABAMA	ALASKA
Thunderstorm Wind	352	1
High Wind	0	95
Marine Thunderstorm Wind	0	0
Strong Wind	22	0
Extreme Cold/Wind Chill	0	10
Cold/Wind Chill	0	0
Marine Strong Wind	0	0
Marine High Wind	0	0