How to pivot log analytics data (Kusto)

Question

I've been hitting my head against a wall on this one. I have the following data (bit like a key value pair) and I need to make the setting names columns instead of rows

let settings = datatable (Location:int , SettingName:string, SettingValue:string)
    [1, "PaperSize", "A4",
     1, "Orientation", "Vertical",
     1, "Colour", "False",
     2, "PaperSize", "A3",
     2, "Orientation", "Vertical",
     2, "Colour", "False",
     3, "PaperSize", "A4",
     3, "Orientation", "Horizontal",
     3, "Colour", "True"];
settings
| evaluate pivot(SettingName)

I'm expecting 3 rows, one for each of the locations along with a column for each of the settings (PaperSize, Orientation, Colour) plus location. But I end up with nine rows and data that doesn't make a lot of sense. That output that I'm trying to get is:


+----------+-----------+-------------+--------+
| Location | PaperSize | Orientation | Colour |
+----------+-----------+-------------+--------+
|        1 | A4        | Vertical    | False  |
|        2 | A3        | Vertical    | False  |
|        3 | A4        | Horizontal  | True   |
+----------+-----------+-------------+--------+

The real data has many settings, so ideally I don't want to hardcode the SettingNames in the query. I've looked at other functions like bags and mv but haven't had any luck. Maybe it's not possible, but any ideas are much appreciated

Alexander Sloutsky Alexander Sloutsky · Accepted Answer · 2020-08-10T13:33:34

You can do it by adding any() aggregation (assuming you are satisfied with any-value)

let settings = datatable (Location:int , SettingName:string, SettingValue:string)
    [1, "PaperSize", "A4",
     1, "Orientation", "Vertical",
     1, "Colour", "False",
     2, "PaperSize", "A3",
     2, "Orientation", "Vertical",
     2, "Colour", "False",
     3, "PaperSize", "A4",
     3, "Orientation", "Horizontal",
     3, "Colour", "True"];
settings
| evaluate pivot(SettingName, any(SettingValue))

How to pivot log analytics data (Kusto)

1 Answers