SOLVED

Pivot sort

%3CLINGO-SUB%20id%3D%22lingo-sub-258817%22%20slang%3D%22en-US%22%3EPivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-258817%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20the%20%22pivot%22%20command%2C%20there%20doesn't%20appear%20to%20be%20a%20way%20to%20sort%20the%20columns...%20am%20I%20wrong%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ETable%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%7C%20project%20Column_A%2C%26nbsp%3BSomeValue%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-keyword%22%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BSomeValue%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-number%22%3E0%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-function%22%3Eevaluate%20%3CSPAN%20class%3D%22hljs-title%22%3Epivot%3C%2FSPAN%3E(Column_A%3CSPAN%20class%3D%22hljs-params%22%3E%2C%20sum(SomeValue%3C%2FSPAN%3E))%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3EIn%20this%20example%2C%20the%20columns%20pivoted%20from%20%22Column_A%22%20will%20show%20up%20in%20no%20discernible%20order.%20Is%20there%20a%20way%20to%20sort%20the%20columns%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-258817%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266807%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266807%22%20slang%3D%22en-US%22%3E%3CP%3EEnhancement%20request!%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-266801%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-266801%22%20slang%3D%22en-US%22%3E%3CP%3EHey%2C%20verified%20that%20and%20indeed%20no%20way%20to%20control%20the%20order%20of%20the%20pivot-based%20columns%20%3A%5C%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-259894%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-259894%22%20slang%3D%22en-US%22%3E%3CP%3EInteresting.%20I'm%20using%20this%20for%20a%20dashboard%20tile%20at%20the%20moment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-259791%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-259791%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Scott%2C%3C%2FP%3E%0A%3CP%3EI%20believe%20you're%20right%20and%20the%20order%20is%20not%20controlled.%20I'll%20forward%20that%20question%20to%20verify%20with%20the%20developers.%3C%2FP%3E%0A%3CP%3EAlso%2C%20please%20note%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FTabular-operators%2Fevaluate-operator%2Fpivot%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Edocumentation%20of%20pivot%3C%2FA%3Ementions%20that%20since%20the%20resulting%20schema%20is%20based%20on%26nbsp%3B%20changing%20result-sets%2C%20it%20is%20not%20a%20stable%20source%20for%20automation%20processes%3A%3C%2FP%3E%0A%3CP%3E%22%3CSPAN%3EThe%20output%20schema%20of%20the%3C%2FSPAN%3E%26nbsp%3B%3CCODE%3Epivot%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3Bplugin%20is%20based%20on%20the%20data%20and%20therefore%20query%20may%20produce%20different%20schema%20for%20any%20two%20runs.%20This%20also%20means%20that%20query%20that%20is%20referencing%20unpacked%20columns%20may%20become%20'broken'%20at%20any%20time.%20Due%20to%20this%20reason%20-%20it%20is%20not%20advised%20to%20use%20this%20plugin%20for%20automation%20jobs.%22%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1016101%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1016101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeconding%20the%20enhancement%20request%2C%20and%20thank%20you%20for%20this%20post%20as%20it%20was%20exactly%20what%20I%20was%20looking%20for!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1104243%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20sort%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104243%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F461464%22%20target%3D%22_blank%22%3E%40hdsheena%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F151992%22%20target%3D%22_blank%22%3E%40Scott%20Allison%3C%2FA%3E%26nbsp%3B%20-%20you%20can%20now%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fprojectreorderoperator%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eproject-reorder%3C%2FA%3E%26nbsp%3Bto%20set%20the%20order%20you%20want%2C%20and%20if%20column%20names%20are%20not%20known%20(since%20the%20data%20set%20here%20is%20dynamic)%20you%20can%20use%20asc%20%2F%20desc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Using the "pivot" command, there doesn't appear to be a way to sort the columns... am I wrong? 

 

For example:

Table

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

 

6 Replies

Hi Scott,

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."

Interesting. I'm using this for a dashboard tile at the moment.

Hey, verified that and indeed no way to control the order of the pivot-based columns :\

Enhancement request! :)

@Scott Allison 

Seconding the enhancement request, and thank you for this post as it was exactly what I was looking for!

best response confirmed by Scott Allison (Frequent Contributor)
Solution

@hdsheena , @Scott Allison  - you can now use project-reorder to set the order you want, and if column names are not known (since the data set here is dynamic) you can use asc / desc.