'metric' column not pivoting in V10.1.1

I currently have a visualization that works fine on grafana V9.1.2
The query has a ‘metric’ column, which pivots the Label column to be series labels.

SELECT $__time( Created ), Label AS metric, Value
FROM Data
WHERE $__timeFilter( Created )
AND Source = “enlighten”
ORDER BY Created ;

When I run the same query in grafana V10.1.1, the data does not pivot. Table view in V9.1.2 shows the ‘metric’ value as a column label, while V10.1.1 shows the metric column as a column.

image

I’m not sure how to move forward on this. Any hints would be appreciated.

Welcome @dbags

I hate that change they did. Look into using group by matrix transformation

You will need to alias the datetime as time

Which visualization are you using?

Thanks heaps. It’s good to know I wasn’t missing something simple.

I’m using the ‘Time series’ visualization.

I recall reading somewhere that the ‘time_sec’ is deprecated and was curious as to why the $__time() macro uses it as the alias. I assume the $__time() macro deprecated too?

Now I am off to learn about ‘group by matrix transformation’. I suppose the upgrade will make me learn a few more things :slight_smile:

OK, that wasn’t hard. I now have a transformation.

but when I switch off table view, I get

image

This is the transformation

yuck one more transformation. I think this is messy there must be a better way. What database are you using, does it allow pivoting?

Oh I think the issue might be that you to select Time Series format and remove all transformations

2 Likes

Thanks yosiasz.
I am using mysql.
The query you propose has the metric values such as 50883055 hard coded into the query.
The problem with that is that I don’t know those metric values in advance.

I have tried your suggestion of removing all tranformations, and change the query Time Series format.

Good news is that has worked! I am now getting a chart. Not properly formatted yet but at least a chart.

Thanks heaps.

1 Like

Thats just sample data to reproduce your problem as we dont have access to your mysql :wink:

I had the same problem with a postgres data source. This saved my day!