Kusto Query problem joining several calculated columns in the SAME table

Question

I'm trying to figure out how to display data in Kusto but I'm running into some issues. I currently have these queries that work:

MyTable1
| where data contains "Books" and page contains "page1" and metricType contains "Count"
| summarize arg_max_value_books = arg_max(value, *) by userId, data
| extend data, userId, booksCount= arg_max_value_books
| project data, userId, booksCount

This returns, and the same for Tools and Cars

Data   | userId   | booksCount
----------------------------------------
Books | 12312   | 4
Books | 51341   | 3
Books | 12245   | 0

MyTable1
| where data contains "Tools" and the page contains "page1" and metricType contains "Count"
| summarize arg_max_value_tools = arg_max(value, *) by userId, data
| extend data, userId, toolsCount= arg_max_value_tools
| project data, userId, toolsCount

Data   | userId   | toolsCount
----------------------------------------
Tools| 12312    | 10
Tools| 51341    | 30
Tools| 12245    | 5


MyTable1
| where data contains "Cars" and page contains "page1" and metricType contains "Count"
| summarize arg_max_value_cars = arg_max(value, *) by userId, data
| extend data, userId, carsCount= arg_max_value_cars
| project data, userId, carsCount

The issue is that the "value" column is different whether the data is Tools, Cars or Books. I need a query to join all of these so that I can see something like:

 userId   | toolsCount | booksCount | carsCount
----------------------------------------------------------------
12312    | 10                | 4                     | 0
51341    | 30                | 3                     | 2
12245    | 5                  | 0                     | 12

I'm a bit confused about whether I should use merge, join, self-join, or something else, and most examples online talk about joining with another table, which is not what I need.

Answer 1

You can use pivot plugin operator in the query instead of joins or merge. Below is the query:

MyTable1 
| where data contains "Books" and page contains "page1"
| extend Data = strcat(Data, 'Count')
| evaluate pivot(Data, max(Value))

Using extend operator, a new column named Data is created and the values in this new column are obtained by concatenating the existing values in the Data column with the string Count using strcat() function. The Data column is used as the pivot column and max(Value) function is used to aggregate the data for each combination of pivot column and other columns.

Output:

userId   | toolsCount | booksCount | carsCount
----------------------------------------------
12312      10                4           0
51341      30                3           2
12245      5                 0           12