Select variable number of columns with MYSQL

I have a table of data recording new downtime reasons every hour for all machines with the following format:

ID t_stamp machine reason minutes
3 2023-01-22 08:00:00 Solder1 Running 60
4 2023-01-22 08:00:00 Solder2 Running 60
5 2023-01-22 08:00:00 Overdraw32 Running 60
6 2023-01-22 08:00:00 Overdraw50 Running 60
7 2023-01-22 08:00:00 Overdraw60 Running 60
8 2023-01-22 09:00:00 Solder1 Stopped 60
9 2023-01-22 09:00:00 Solder2 Stopped 60
10 2023-01-22 09:00:00 Overdraw32 Stopped 60
11 2023-01-22 09:00:00 Overdraw50 Stopped 60
12 2023-01-22 09:00:00 Overdraw60 Stopped 60
13 2023-01-22 10:00:01 Solder1 Stopped 60
14 2023-01-22 10:00:01 Solder2 Stopped 60
15 2023-01-22 10:00:01 Overdraw32 Stopped 60
16 2023-01-22 10:00:01 Overdraw50 Stopped 60
17 2023-01-22 10:00:01 Overdraw60 Stopped 60

I would like a query that can return the daily sums of the minutes for each reason and make a column for each distinct reason like this (To work with a bar chart I think I would have to have to return only the data for 1 machine at a time - so this would be for only 1 machine so I think I need to loop through the distinct reasons and then use those reasons as the header name for my query):

time Running Stopped
2023-01-22 60 120

That is called a PIVOT. Your DB can do it for you. You can script it for a bit more flexibility. Even my view() expression function in Simulation Aids can do this.

Numerous explanatory topics here.

I was able to accomplish this with SUM(CASE):

SELECT t_stamp as time, 
SUM(CASE WHEN reason = 'Running' THEN minutes ELSE 0 END) AS Running, 
SUM(CASE WHEN reason = 'Stopped' THEN minutes ELSE 0 END) AS Stopped
FROM downtimeTracking
GROUP BY t_stamp;

Here is the result:


There won't always just be Running and Stopped, and there could also be a custom input that I can't predict so the query needs to be able to loop through a distinct unknown number of reasons too.

What database are you using?

I was able to do it in an actual MySQL database using the GROUP_CONCAT function.

Here is the query:

SET @sql = NULL;
      'SUM(CASE WHEN reason = ''',
      ''' THEN minutes ELSE 0 END) AS `',
      reason, '`'
  ) INTO @sql
FROM test_schema.downtimeTracking;
SET @sql = CONCAT('SELECT t_stamp as time, ', @sql, '  FROM test_schema.downtimeTracking GROUP BY t_stamp');
PREPARE stmt FROM @sql;

Here is the result:

Note: My database name is test_schema, and my table name is downtimetracking. Obviously, these will need to be changed to match the names of your schema and table. Otherwise, the function should work as expected.

Thanks Justin,

I am using Mysql and was able to use this but I have a couple questions. It doesn't seem to give me the last column name as Stopped, any idea how to fix that?
And it doesn't work as a named query in Ignition, any idea how to get around that?

It would be so much simpler if mySql had a built in PIVOT function, but for this case, one approach would be to parse the alias from the field name of the last column.

EDIT: I realized that the actual problem is the default GROUP_CONCAT() limit of 1024 bytes. Miraculously, it appears that the break point in your usage case was actually at a point that would return a result instead of an error.

Simply add this to first line of the original procedure I posted, and it will correct the issue:

SET SESSION group_concat_max_len = 1000000;

Save it in the database as a stored procedure and then Call the stored procedure from Ignition.

SET @sql = CONCAT('SELECT CAST(t_stamp AS DATE) AS time, ', @sql, ' FROM schema.downtime_minutes WHERE t_stamp > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY) GROUP BY CAST(t_stamp AS DATE)');

This line seems to work, but if I want to pass in like a @start and @end it won't let me do that.

SET @sql = CONCAT('SELECT CAST(t_stamp AS DATE) AS time, ', @sql, ' FROM schema.downime_minutes WHERE t_stamp BETWEEN "',@start, '" AND "',@end, '" GROUP BY CAST(t_stamp AS DATE)');

This works.

I just checked this with live data, and it seems as though I am not selecting on a single machine.
By your example the 300 running and 300 stopped minutes per hour is totalizing all 5 machines.

Understood. To add machine info to the pivot and break up the results, simply add machine to your SELECT statement and GROUP BY clause:

SET @sql = CONCAT('SELECT machine, CAST(t_stamp AS DATE) AS time, ', @sql, ' FROM luvata.downtime_minutes WHERE t_stamp BETWEEN "',@start, '" AND "',@end, '" GROUP BY machine, CAST(t_stamp AS DATE)');