Sep 20 2018
- last edited on
Apr 07 2022
Using the "pivot" command, there doesn't appear to be a way to sort the columns... am I wrong?
| project Column_A, SomeValue
| where SomeValue > 0
| evaluate pivot(Column_A, sum(SomeValue))
In this example, the columns pivoted from "Column_A" will show up in no discernible order. Is there a way to sort the columns?
Sep 20 2018 10:00 AM
I believe you're right and the order is not controlled. I'll forward that question to verify with the developers.
Also, please note the documentation of pivot mentions that since the resulting schema is based on changing result-sets, it is not a stable source for automation processes:
"The output schema of the
pivot plugin is based on the data and therefore query may produce different schema for any two runs. This also means that query that is referencing unpacked columns may become 'broken' at any time. Due to this reason - it is not advised to use this plugin for automation jobs."
Jan 26 2022 07:05 AM
Working with similar kind of problem with below query:
date_of_month | Hour0 | Hour1 | Hour10 | Hour11 ....... Hour9
What I expect:
date_of_month | Hour0 | Hour1 | Hour2 | Hour3 ...... Hour23
If I try to use column name like Hour0.... Hour23 getting below error
project-reorder: Failed to resolve attribute as a column entity: Hour0
Jan 27 2022 02:29 AM
You can use granny_asc now https://docs.microsoft.com/en-gb/azure/data-explorer/kusto/query/projectreorderoperator
Heartbeat | summarize successCount = count(Computer) by bin(TimeGenerated, 1h) | project date_of_month = format_datetime(TimeGenerated, 'yyyy-MM-dd'), hour = strcat("Hour", datetime_part("hour", TimeGenerated)), successCount | evaluate pivot(hour, sum(successCount)) | project-reorder date_*, Hour* granny-asc