How to summarize by an unknown number of columns?

Question

I would like to create summaries for the results of a certain type of task across multiple geographic regions. I currently have the following dummy query:

datatable(env_cloud_location : string, result : string )[
    "East Asia", "Passed",
    "East Asia", "Failed",
    "East Asia", "Error",
    "East US", "Passed",
    "East US", "Error",
    "East US", "Passed",
    "North Europe", "Failed",
    "North Europe", "Error",
    "North Europe", "Error",
]
| summarize 
    eastAsia = countif(env_cloud_location  == "East Asia"),
    eastUS = countif(env_cloud_location  == "East US"),
    northEurope = countif(env_cloud_location == "North Europe")
    by result

Now, I suppose I don't know all the possible values of "env_cloud_location" and there in fact are anywhere from 5 to 12, how can I summarize such that each of these locations, however many there are, gets their own column? Expected output in the format:

result	eastAsia	eastUS	northEurope
Passed	1	2	0
Failed	1	0	1
Error	1	0	2

David דודו Markovitz · Accepted Answer · 2022-10-05 14:20:57Z

2

Yet another pivot question

datatable(env_cloud_location : string, result : string )[
    "East Asia", "Passed",
    "East Asia", "Failed",
    "East Asia", "Error",
    "East US", "Passed",
    "East US", "Error",
    "East US", "Passed",
    "North Europe", "Failed",
    "North Europe", "Error",
    "North Europe", "Error",
]
| evaluate pivot(env_cloud_location)

result	East Asia	East US	North Europe
Passed	1	2	0
Failed	1	0	1
Error	1	1	2

Fiddle

answered Oct 5, 2022 at 14:20

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

UndyingJellyfish Over a year ago

I always forget that Pivot tables exist. Thanks for reminding me.

Collectives™ on Stack Overflow

How to summarize by an unknown number of columns?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related