Multiple table via single query on MSSQL

Grafana Time Series Panel
1

  • What Grafana version and what operating system are you using?

    Grafana v9.1.3 / Microsoft SQL

  • What are you trying to achieve?

    There are data of TIME, TEMP, PRESS, and NO in one table.
    The NO column is the ID of the sensor.
    I want to draw a time series graph of TEMP and PRESS by sensor ID.

image
image2350×720 53.5 KB

  • How are you trying to achieve it?

I think we should make several tables for each sensor ID ‘NO’. But I don’t know what to do.

  • What did you expect to happen?

There are several tables as shown below, and several graphs want to be drawn by NO.

image
image2340×782 91.6 KB

image
image2286×612 116 KB

  • Can you copy/paste the configuration(s) that you are having problems with?

The picture below is my query sentence.

image
image1124×396 32 KB

I really want someone to help me.

2

What happens when you change the visualization to Time Series graph? What error message(s), if any, are displayed?

Also, I believe you need to insert the words “as time” after your first select statement, like this

$__timeEpoch(CREATE_TIME) as 'time',

or maybe just

$__timeEpoch(CREATE_TIME) as time,
3

one approach is following pivot table.

image
image1096×479 53 KB

SELECT * FROM (
  SELECT
   $__timeEpoch(CREATETIME),
  TEMP,  concat('TEMP_',NO) TMP
  FROM MACHINE_ROOM_SENSOR_HIST
  where $__timeFilter(CREATETIME) and NO in (1,2,3)
  
) StudentResults
PIVOT (
  SUM(TEMP)
  FOR TMP
  IN (
    [TEMP_1],
    [TEMP_2],
    [TEMP_3]
  )
) AS PivotTable
1 Like
4

Thanks to yosiasz, I was able to solve the problem.

1 Like