Azure

Azure Information Explorer – Kusto Question – Rework Rows To Columns

Introduction 

 

In my earlier submit, I mentioned getting the end result set which lies between the given date vary. This time, let’s take one other attention-grabbing instance, the place we have to rework the variety of rows into the variety of columns as our end result set.

 

Contemplate the under knowledge for which we have to write a question:

  1. let demoData = datatable(Setting: string, Function:string, Location:string, Model: string)    
  2. [      
  3.    “dev”“Feature1”“Site1”“v1”,      
  4.    “test”“Feature1”“Site2”“v2”,      
  5.    “prod”“Feature1”“Site3”“v3”,     
  6.    “dev”“Feature2”“Site1”“v4”,      
  7.    “test”“Feature2”“Site4”“v5”,      
  8.    “dev”“Feature3”“Site1”“v6”,      
  9.    “test”“Feature3”“Site2”“v7”,      
  10.    “prod”“Feature3”“Site3”“v7”      
  11. ];    

Question description

 

Generate outcomes set in such a manner so that there’s precisely one row for every Function.

 

Question

  1. let versionList = my_data  
  2. | summarize d = make_bag(pack(strcat(Setting,“Model”), Model)) by Function  
  3. | consider bag_unpack(d);  
  4. let locationList = my_data  
  5. | summarize d = make_bag(pack(strcat(Setting,“Location”), Location)) by Function  
  6. | consider bag_unpack(d);  
  7. versionList  
  8. be a part of locationList on Function  
  9. | project-away Feature1   

Now when you run the question, you’re going to get the under output:

 

 

When it comes to expectation, the end result seems good, however let’s make it extra readable by transferring the placement and model subsequent to one another.

 

This may be achieved by appending one other pipe for project-reorder. It might change our question to one thing like this:

  1. let versionList = my_data  
  2. | summarize d = make_bag(pack(strcat(Setting,“Model”), Model)) by Function  
  3. | consider bag_unpack(d);  
  4. let locationList = my_data  
  5. | summarize d = make_bag(pack(strcat(Setting,“Location”), Location)) by Function  
  6. | consider bag_unpack(d);  
  7. versionList  
  8. be a part of locationList on Function  
  9. | project-away Feature1  
  10. | project-reorder Function , * asc  

Now, when you run above question, you will notice the output as proven under:

 

Azure Data Explorer - Kusto Query - Transform Rows To Columns

 

I hope that you just loved this knowledge transformation question. 

 

Glad Kustoing!

Show More

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button