pivot plugin
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
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.
Note
If the OutputSchema is not specified, the output schema of the pivot plugin is based on the input data. Therefore, multiple executions of the plugin using different data inputs, may produce different output schema. This also means that the query that is referencing unpacked columns may become 'broken' at any time. For this reason, we do not recommend using this plugin for automation jobs without specifying the OutputSchema function.
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 |