I got a table like this in Azure analytics with Kusto for the game I'm working on

datatable (ID_player:string, Timestamp:timespan, monster1:int, monster2:int, monster3:int)
[
     "aaa", "12:00:00", 1,2,3
    ,"aaa", "12:10:00", 4,7,0
    ,"bbb", "12:30:00", 0,2,1
]

Basically, I need to switch to a format like this one

ID_Player     Timespamp     Monster     Quantity
aaa           12:00:00      Monster1      1
aaa           12:00:00      Monster2      2
aaa           12:00:00      Monster3     3
aaa           12:10:00      Monster1     4
aaa           12:10:00      Monster2      7
aaa           12:10:00      Monster3      0
bbb           12:30:00      Monster1      0
bbb           12:30:00      monster2      2
bbb           12:30:00      Monster3      1

ny idea? I tought to use a series of CASE WHEN But I don't think it's the right solution. Thanks in advance!!!

CodePudding user response：

you could use a combination of pack() & mv-apply.

for example:

datatable (ID_player:string, Timestamp:timespan, monster1:int, monster2:int, monster3:int)
[
     "aaa", "12:00:00", 1,2,3
    ,"aaa", "12:10:00", 4,7,0
    ,"bbb", "12:30:00", 0,2,1
]
| mv-apply c = pack("monster1", monster1, "monster2", monster2, "monster3", monster3) on (
    extend Monster = tostring(bag_keys(c)[0])
    | extend Quantity = tolong(c[Monster])
)
| project-away monster*, c