Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading