Azure Data Explorer - Kusto Query - Transform Rows To Columns

Introduction

In my previous post, I discussed about 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.

let demoData = datatable(Environment: string, Feature: string, Location: string, Version: string)
[
    "dev", "Feature1", "Site1", "v1",
    "test", "Feature1", "Site2", "v2",
    "prod", "Feature1", "Site3", "v3",
    "dev", "Feature2", "Site1", "v4",
    "test", "Feature2", "Site4", "v5",
    "dev", "Feature3", "Site1", "v6",
    "test", "Feature3", "Site2", "v7",
    "prod", "Feature3", "Site3", "v7"
];

Query description

Generate results set in such a way that there is exactly one row for each Feature.

Query

let versionList = my_data
| summarize d = make_bag(pack(strcat(Environment, "Version"), Version)) by Feature
| evaluate bag_unpack(d);

let locationList = my_data
| summarize d = make_bag(pack(strcat(Environment, "Location"), Location)) by Feature
| evaluate bag_unpack(d);

versionList
| join locationList on Feature
| 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 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.

let versionList = my_data
| summarize d = make_bag(pack(strcat(Environment, "Version"), Version)) by Feature
| evaluate bag_unpack(d);

let locationList = my_data
| summarize d = make_bag(pack(strcat(Environment, "Location"), Location)) by Feature
| evaluate bag_unpack(d);

versionList
| join locationList on Feature
| project-away Feature1
| project-reorder Feature , * asc

Now, if you run the above query, you will see the output as shown below.

I hope that you enjoyed this data transformation query.

Happy Kustoing!