How to unpivot columns in kusto/kql/azure and put multiple columns into one

Question

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!!!

Answer 1

Here are 3 more straightforward solutions, based on mv-expand & pack_dictionary() / pack_array()

pack_dictionary() + mv-expand kind = array

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-expand kind = array monster = pack_dictionary("Monster1", monster1, "Monster2", monster2, "Monster3", monster3)
| extend Monster = tostring(monster[0]), Quantity = toint(monster[1])
| project-away monster*

Fiddle

pack_array()x2 + mv-expand

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-expand     Monster  = pack_array("Monster1", "Monster2", "Monster3") to typeof(string)
               ,Quantity = pack_array( monster1,   monster2,   monster3 ) to typeof(int)
| project-away  monster*

Fiddle

pack_array() + mv-expand with_itemindex

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-expand with_itemindex = i Quantity = pack_array(monster1, monster2, monster3) to typeof(int)
| extend Monster = strcat("Monster", tostring(i + 1))  
| project ID_player, Timestamp, Monster, Quantity

Fiddle

Answer 2

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

ID_player	Timestamp	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