Graph with PIVOT returns: found no column named "time"

Hello, everybody.

I wrote an SQL Server with PIVOT where my intention is to show the factory’s daily goals, and what have been made in every day. I’ll show you my code:

DECLARE @columns_pivot AS nvarchar(MAX), @command_sql AS nvarchar(MAX)

SET @columns_pivot = 
	STUFF((
		SELECT
			DISTINCT ',' + QUOTENAME(CONVERT(varchar, C.con_dt_fim, 103))
		FROM
			Tracking AS R
			INNER JOIN Count AS C ON C.rastr_id = R.rast_id AND C.con_dt_exc IS NULL
		WHERE
			R.op_id = $op_id
		GROUP BY
			DailyGoal, C.con_dt_fim
        FOR XML PATH('')
    ), 1, 1, '')
PRINT @colunas_pivot

SET @comando_sql = '
	SELECT * FROM(
		SELECT
			CONVERT(varchar, C.con_dt_end, 103) AS time
			,SUM(C.con_qtde_end) AS made
			,DailyGoal AS goal
		FROM
			Tracking AS R
			INNER JOIN Count AS C ON C.rastr_id = R.rast_id AND C.con_dt_exc IS NULL
		WHERE
			R.op_id = $op_id
		GROUP BY
			DailyGoal, C.con_dt_end) in_lines
	PIVOT(
		SUM(made) FOR time IN ('+@columns_pivot+')) in_columns
	ORDER BY 1'
PRINT @command_sql

EXECUTE(@command_sql)

This code runs very well in SQL Server, but when I paste at Grafana (time series) returns the message found no column named “time”. I can’t understand where and how must I write this time part, could you help me, please?

Do you want it to display as a Graph or Table?

Maybe debug by turning “Format as” at bottom of edit screen to Table instead of Time Series

I want to display it as a Graph. Actually it runs well when I set “Format as” as Table and choose Table Visualization.

image

But if I choose Graph Visualization it doesn’t show any data:

image

My DATETIME type (103) is wrong, perhaps?

Welcome,

Please provide some sample data (even bogus/obfuscated data)

--DDL
create table #Tracking(rast_id int, op_id int);
create table #count(rastr_id int, con_dt_fim int, con_dt_exc int);

--DML
insert into #Tracking
values(1,3)

Also please provide how $op_id is built?

etc. Since we do not have access to your database, doing the above helps us emulate your data so that we can help you find a solution. Otherwise we would just be guessing and going back and forth.

Here is $op_id building:

image

About your other questions, I’m not sure how could I let you know what you want. But every data you wrote on your create tables are as INT in my database. For exemple, when I write ‘101711’ in $op_id, I got these informations:

image

And this runs well when I choose Table Visualization, but retuns message of “no data” when I choose Graph Visualization.

1 Like

Thanks for the $op_id, simple enough.

But for the sample data, you don’t need to “let me know” :wink: you just let the data talk by providing sample data via the following

-DDL
create table #Tracking(rast_id int, op_id int);
create table #count(rastr_id int, con_dt_fim int, con_dt_exc int);

--DML
insert into #Tracking
values(1,3)

remember the above is just a guess from looking at your sql query. but you need to provide me the real table column data types. as you can see I just used int just as a sample but I know you have datetime columns and maybe other type of columns. we cant see your database so you give us an insight into your database by providing sample data.