Azure Data Explorer - Kusto Query - Transform Rows To Columns

Introduction 

 
In my previous post, I discussed getting the result set which lies between the given date range. This time, let’s take another interesting example, where we need to transform the number of rows into the number of columns as our result set.
 
Consider the below data for which we need to write a query:
  1. let demoData = datatable(Environment: string, Feature:string, Location:string, Version: 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. ];    
Query description
 
Generate results set in such a way so that there is exactly one row for each Feature.
 
Query
  1. let versionList = my_data  
  2. | summarize d = make_bag(pack(strcat(Environment,"Version"), Version)) by Feature  
  3. | evaluate bag_unpack(d);  
  4. let locationList = my_data  
  5. | summarize d = make_bag(pack(strcat(Environment,"Location"), Location)) by Feature  
  6. | evaluate bag_unpack(d);  
  7. versionList  
  8. join locationList on Feature  
  9. | project-away Feature1   
Now if you run the query, you will get the below output:
 
Azure Data Explorer - Kusto Query - Transform Rows To Columns
 
In terms of expectation, the result looks good, but let’s make it more readable by moving the location and version next to each other.
 
This can be achieved by appending another pipe for project-reorder. It would change our query to something like this:
  1. let versionList = my_data  
  2. | summarize d = make_bag(pack(strcat(Environment,"Version"), Version)) by Feature  
  3. | evaluate bag_unpack(d);  
  4. let locationList = my_data  
  5. | summarize d = make_bag(pack(strcat(Environment,"Location"), Location)) by Feature  
  6. | evaluate bag_unpack(d);  
  7. versionList  
  8. join locationList on Feature  
  9. | project-away Feature1  
  10. | project-reorder Feature , * asc  
Now, if you run above query, you will see the output as shown below:
 
Azure Data Explorer - Kusto Query - Transform Rows To Columns
 
I hope that you enjoyed this data transformation query. 
 
Happy Kustoing!