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.
But if I choose Graph Visualization it doesn’t show any data:
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:
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:
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” 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.