Multiple timeseries, pivot column

I’d like to know if it’s possible and how to render multiple timeseries defining one column of the result set as a pivot column. For some queries I don’t know in advance how many series I’m getting hence is not possible to create a multi column query. At the moment I’m using tablefunc in postgresql but it’s very annoying solution indeed we need to know the columns name in advance

Result set is in this form:

t, series_name, value
0.0, s1, 12
0.0, s2, 13
1.0, s1, 14
1.0, s2, 12

it would be very good if we could define the column series_name as the pivot column and have grafana automatically plots two series, one per s1 lines and one per s2 lines

i think that what you want is already, can you post one of your queries?

SELECT t, series_name, value
FROM t_measurements
WHERE t>now()-‘5 days’::interval;

Mmm sorry i dont understan to you… can you explain a little better?

Grafana is able to produce graphs out of a single query with result set such as rows of times and value, in order to have another graph we can add a second query on the same graph like:

q1) SELECT time, value FROM test WHERE series_name = ‘A’;
q2) SELECT time, value FROM test WHERE series_name = ‘B’;

the issue is that in some cases we don’t know in advance the name of series and most of all how many series we have, the ideal would be to do:

SELECT time, series_name, value FROM test;

and then tell Graphan that series_name is the column it has to use for pivoting the values producing out of it multiple lines.

You can do that!!

SELECT
time,
value,
metric
FROM test

it works indeed! Thanks

Hi there. Same problem here with Postgres. I can’t make it work.
My query looks something like this:

SELECT
$__time(timestamp),
m_value as value,
m_point as metric
FROM
measurement
WHERE $__timeFilter(timestamp) AND m_point IN ($selected_m_point)
ORDER BY timestamp

The “measurement” table has columns: timestamp, m_point and m_value

On graphic, I see only series: value and metric. I expect to see all from variables: m_1, m_2 and so on.
Am I missing something?

Many thanks.

@emarkosavic

please provide some sample data for measurement? Also what version of grafana and are you trying to plot it on a time series?

I’m using the latest Grafana v9.4.0-pre. I’m trying to plot it on the Time series panel without any transformation.
Measurement data looks like this:
id,m_point, timestamp, m_value
1197769,397,2022-12-13 22:27:06.641000 +00:00,9688031
1197768,399,2022-12-13 22:27:06.039000 +00:00,6312563
1197767,401,2022-12-13 22:27:05.491000 +00:00,5334672

1 Like

To illustrate better here is what I need:

image

I’ve tried with Transformation ‘Partition by values’ it works, but it keeps measuring_point together with the measurement. The problem is I can’t make stacking work with that.

This is more a question of sql syntax (might be best to ask in a sql forum https://forums.sqlteam.com/) than grafana

DECLARE @columns varchar(200), @SqlStatement NVARCHAR(MAX)
SET @columns = N'';
SELECT @columns  += CONCAT ('[', cast(m_point as varchar(100)) ,']',',') from emarko
SET @columns = LEFT (@columns, LEN (@columns) - 1)

SET @SqlStatement = N'
SELECT * 
FROM (
        SELECT m_date as time, m_value, m_point FROM emarko
        ) r
PIVOT (
	max(m_value)
	FOR m_point
	IN (' + @columns + ')
) AS PivotTable
';
 
exec(@SqlStatement)

Thanks, @yosiasz for your help.
I’ve managed to solve this using the new ‘Partition by values’ Transformation. It does exactly what it says it does.
The only problem was the offset of the timestamp column if you want to do stacking of the series.
Luckily it is easy to fix if you are using a timescale extension with this functions:

time_bucket_gapfill(‘1 minute’, timestamp) AS minute,
interpolate(avg(“measurement” * dec.ct)) as value

1 Like

I dont see that transformation but this also worked for me

2 Likes

Yes, that transformation also works. Unfortunately, I couldn’t figure out how to transform Time/m_point to pure Time, didn’t know for “Convert filed type” transformation.

For the others, this transformation from Grafana 9.3 works the same: