Pivoting transformation

Grafana Table Panel
1

  • What Grafana version and what operating system are you using?
    grafana 10.4.3 (latest)

  • i want to create single table panel based on pivoting (create a dynamic amount of column

  • I hoped for grafana transformations to cover this need but did not find a working transformation.

  • Desired result in one grafana table panel when a user selects 2 sensors named “sensorx” , sensor_abc

TIME sensorx	VALUE sensorx	TIME sensor_abc	VALUE sensor_abc
30/05/2024 23:33	24740	30/05/2024 23:34	22207
30/05/2024 23:43	24740	30/05/2024 23:43	22206
30/05/2024 23:47	24738	30/05/2024 23:47	22199
31/05/2024 00:01	24738	30/05/2024 23:56	22195
31/05/2024 00:05	24736	31/05/2024 00:01	22191
31/05/2024 00:10	24735	31/05/2024 00:05	22190
31/05/2024 00:19	24736	31/05/2024 00:10	22186
31/05/2024 00:28	24737	31/05/2024 00:18	22181
31/05/2024 00:33	24733	31/05/2024 00:23	22181
31/05/2024 00:38	24731	31/05/2024 00:27	22181
31/05/2024 00:47	24731	31/05/2024 00:32	22176
31/05/2024 01:01	24734	31/05/2024 00:36	22171
31/05/2024 01:10	24729	31/05/2024 00:45	22166
31/05/2024 01:24	24724	31/05/2024 00:59	22159
31/05/2024 01:33	24724	31/05/2024 01:03	22159

source sql data (simplified) is:

time	value	sensor
30/05/2024 23:33	24740	sensorX
30/05/2024 23:43	24740	sensorX
30/05/2024 23:47	24738	sensorX
31/05/2024 00:01	24738	sensorX
31/05/2024 00:05	24736	sensorX
31/05/2024 00:10	24735	sensorX
31/05/2024 00:19	24736	sensorX
31/05/2024 00:28	24737	sensorX
31/05/2024 00:33	24733	sensorX
31/05/2024 00:38	24731	sensorX
30/05/2024 23:34	22207	sensor_abc
30/05/2024 23:43	22206	sensor_abc
30/05/2024 23:47	22199	sensor_abc
30/05/2024 23:56	22195	sensor_abc
31/05/2024 00:01	22191	sensor_abc
31/05/2024 00:05	22190	sensor_abc
31/05/2024 00:10	22186	sensor_abc
31/05/2024 00:18	22181	sensor_abc
31/05/2024 00:23	22181	sensor_abc
31/05/2024 00:27	22181	sensor_abc

note:
grafana repeat option in a panel shows exactly correct data, but that does not allow the user to download the desired result in one download action.

2

i would leverage methods within the datasource itself to do this

is it mysql, postgres?

3

postgres. but not sure how to deliver a dynamic set of columns, depending on how many sensors are queried.
in python i would see no problem, especially with some orm (django / sqlalchemy)
is there an sql function you would recommend ?

tobias.

4

better off asking in a postgres forum

in mssql it is the pivot command with dynamic sql

5

thx. i took a look. perhaps it’s technicaly possible if i write ad hoc functions from within grafana to define (adhoc) return table definitions.

CREATE OR REPLACE FUNCTION dynamic_pivot(sensor_names VARCHAR[])
RETURNS TABLE (rn INTEGER, "TIME_sensorX" TIMESTAMP, "VALUE_sensorX" REAL, "TIME_sensor_abc" TIMESTAMP, "VALUE_sensor_abc" REAL)
LANGUAGE plpgsql

but something like that requires write access to my db. not sure i will go that route.
using a json / graphql based model: perhaps a better idea? (infinity looks promising)

1 Like
6

And why not? Do you not have access to your db?
you can still do it withing grafana without having to create that function in your db.

infinity is for rest api calls and yes that could be used if you create a rest api against your db.

;with src
as
(
select '30/05/2024 23:33' as [time],	24740 as value, 'sensorX' as sensor union
select '30/05/2024 23:43' as [time],	24740	, 'sensorX' as sensor union
select '30/05/2024 23:47' as [time],	24738	, 'sensorX' as sensor union
select '31/05/2024 00:01' as [time],	24738	, 'sensorX' as sensor union
select '31/05/2024 00:05' as [time],	24736	, 'sensorX' as sensor union
select '31/05/2024 00:10' as [time],	24735	, 'sensorX' as sensor union
select '31/05/2024 00:19' as [time],	24736	, 'sensorX' as sensor union
select '31/05/2024 00:28' as [time],	24737	, 'sensorX' as sensor union
select '31/05/2024 00:33' as [time],	24733	, 'sensorX' as sensor union
select '31/05/2024 00:38' as [time],	24731	, 'sensorX' as sensor union
select '30/05/2024 23:34' as [time],	22207	, 'sensor_abc' union
select '30/05/2024 23:43' as [time],	22206	, 'sensor_abc' union
select '30/05/2024 23:47' as [time],	22199	, 'sensor_abc' union
select '30/05/2024 23:56' as [time],	22195	, 'sensor_abc' union
select '31/05/2024 00:01' as [time],	22191	, 'sensor_abc' union
select '31/05/2024 00:05' as [time],	22190	, 'sensor_abc' union
select '31/05/2024 00:10' as [time],	22186	, 'sensor_abc' union
select '31/05/2024 00:18' as [time],	22181	, 'sensor_abc' union
select '31/05/2024 00:23' as [time],	22181	, 'sensor_abc' union
select '31/05/2024 00:27' as [time],	22181	, 'sensor_abc' 
)
SELECT [time] as time, [sensor_abc], [sensorX]
FROM  
(
  SELECT sensor as metric
      ,[time]
      ,value
  FROM src
) AS SourceTable  
PIVOT  
(  
  sum(value)  
  FOR metric IN ([sensor_abc], [sensorX])  
) AS PivotTable;

image