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:
- let demoData = datatable(Setting: string, Function:string, Location:string, Model: 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”
- ];
Question description
Generate outcomes set in such a manner so that there’s precisely one row for every Function.
Question
- let versionList = my_data
- | summarize d = make_bag(pack(strcat(Setting,“Model”), Model)) by Function
- | consider bag_unpack(d);
- let locationList = my_data
- | summarize d = make_bag(pack(strcat(Setting,“Location”), Location)) by Function
- | consider bag_unpack(d);
- versionList
- | be a part of locationList on Function
- | 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:
- let versionList = my_data
- | summarize d = make_bag(pack(strcat(Setting,“Model”), Model)) by Function
- | consider bag_unpack(d);
- let locationList = my_data
- | summarize d = make_bag(pack(strcat(Setting,“Location”), Location)) by Function
- | consider bag_unpack(d);
- versionList
- | be a part of locationList on Function
- | project-away Feature1
- | project-reorder Function , * asc
Now, when you run above question, you will notice the output as proven under:
I hope that you just loved this knowledge transformation question.
Glad Kustoing!
