Kusto: How to convert columns to rows and summarize by them

user_15306571 0
Viewed 2789

I have a table like this: -

Type | shown | eaten  |
-----------------------
a    |  5    |   2    |
b    |  6    |   23   |
c    |  8    |   12   |
d    |  19   |   11   |
e    |  2    |   22   |
-----------------------

I want to convert it like this: -

Type | action | count |
-----------------------
a    | shown  |  5    |
a    | eaten  |  2    |
b    | shown  |  6    |
b    | eaten  |  23   |
c    | shown  |  8    |
c    | eaten  |  12   |
d    | shown  |  19   |
d    | eaten  |  11   |
e    | shown  |  2    |
e    | eaten  |  22   |
-----------------------

How to write a kql query for this?

kql azure-data-explorer
2 Answers

Here is one option (note that I changed some of the column names to capital case so it does not need escaping).

datatable(Type: string, shown: int, eaten: int) ["a", 5, 2,
"b", 6, 23,
"c", 8, 12,
"d", 19, 11,
"e", 2, 22]
| extend Temp = pack("shown",shown,"eaten",eaten )
| mv-expand kind=array Temp
| project Type, Action=tostring(Temp[0]), Count=toint(Temp[1])
user_101679140

Here you go:

let Data = datatable(Type:string, Shown:long, Eaten:long) [
    "a", 5, 2,
    "b", 6, 23,
    "c", 8, 12
];
Data
| project Type, Pack = pack("Shown", Shown, "Eaten", Eaten)
| mv-expand Pack
| extend Action = tostring(bag_keys(Pack)[0])
| project Type, Action, Count = tolong(Pack[Action])
user_21450970
Related