Azure Data Explorer - Kusto Query - Transform Rows To Columns

In my previous post, I discussed about getting the result set which lies between the given date range. This time, let’s take an another interesting example, wherein we need to transform the number to rows into number of columns as our result set.

Consider below data for which we need to write 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 result set in such a way that there should be 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:

In terms of expectation, the result looks good, but let’s make it more readable by moving location and version next to each other. 

This can be achieved by appending another pipe for project-reorder and 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:

I hope you enjoyed this data transformation query.

Happy kustoing!