Question 106018 in Azure Question posted in
The official documentation to get you started can be found here.

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

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search