Merging results of multiple queries into table (Use label values as columns)

[dgrfl]

• What Grafana version and what operating system are you using?
  I am using Grafana 9.4.7 on a RHEL 8.x VM

• What are you trying to achieve?
  I have 2 metrics JvmActiveTotal and JvmInactiveTotal, each with the same label “env” containing dev / qa / uat.
  The “historical information” is irrelevant, only the last value needs to be shown
  I want a table with 2 rows JvmActiveTotal and JvmInactiveTotal and dynamic columns per environment (dev / qa / uat)

• How are you trying to achieve it?
  I tried as follows

• table panel
• 2 queries (1 per metric) : Format Table + Type Instant
• Transformation : Group By Matrix : Column=“env” Row=“name” Cell Value =Value

• What happened?
  When only first query is defined, the result is ok : 1 row, with three columns, with correct values (without timestamp)
  When second query is added to the panel, the table shows timestamp and all labels with a “listbox” at the bottom where I can select Value A or Value B.

I tried to resolve by adding a Merge Transformation first before the Group By Matrix,
The Group By Matrix now requires Value to be either Value #A or #B.
Now 2 rows appear , but only one of the rows contains the actual correct values : the other row contains blanks.

When using a [Merge Transf. + Reorganize + Group By matrix ] with the reorganize to rename Value #A and Value #B to “Val”, the column headers are not dev / qa / uat but three times Val.

• What did you expect to happen?
  I would expect a “merge transformation” that would place the actual values in the same “Value” variable , so that it can be displayed correctly.

Or is there a better approach to display results of different queries in 1 overview with the values of a label as columns?

[dgrfl]

I managed to resolve it myself.

Starting point
Table panel with 2 queries that have identical set of labels. 1 label contains values that you want to have as columns (env = dev / qa / …)

Add transformations

Merge Transformation : all the actual values are stored as Value #A, #B, …
Add field from calculation : Reduce Row … Select the Value#A #B fields and calculate the Total
Grouping to Matrix : Column = env, Row= name Cell Value = Total

[dgrfl]

An additional question.

The table panel now contains 3 queries:

• JvmActiveTotal
• JvmInactiveTotal
• count by (env) (sib_depth{ }/ sib_max{} > 0.1)

The first 2 queries have a “__ name __” , “env” and actual value.
The third query only has “env” and the actual value.

The merge and group by transformations combine the incorrect values, because the query does not have a name.

Can one adapt the query so that it has a meta “__ name __” field?