# Select variable number of columns with MYSQL

**URL:** <https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885>\
**Category:** Ignition\
**Created:** [January 22, 2023, 3:23pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885 "2023-01-22T15:23:53Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [January 22, 2023, 3:23pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/1 "2023-01-22T15:23:53Z")

</div>

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 |
| | | |

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 22, 2023, 6:26pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/2 "2023-01-22T18:26:20Z")

</div>

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](https://forum.inductiveautomation.com/search?q=pivot).

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [January 22, 2023, 6:37pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/3 "2023-01-22T18:37:30Z")

</div>

I was able to accomplish this with [SUM(CASE):](http://sqlfiddle.com/#!9/2d181e/2)

```auto
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](http://sqlfiddle.com/#!9/2d181e/2):

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/7/4753014e335245b4ca1ec8b68e0b126530898cad.png)

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [January 22, 2023, 7:14pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/4 "2023-01-22T19:14:02Z")

</div>

Thanks.

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.

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [January 22, 2023, 7:29pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/5 "2023-01-22T19:29:11Z")

</div>

~~What database are you using?~~

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [January 22, 2023, 8:33pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/6 "2023-01-22T20:33:18Z")

</div>

I was able to do it in an actual MySQL database using the [GROUP\_CONCAT](https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php) function.

Here is the query:

```auto
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN reason = ''',
      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;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

```

Here is the result:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/4/84b530c041308d8ea900031a984183b9375a8ce3.png)

**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.

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [January 28, 2023, 8:30pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/7 "2023-01-28T20:30:08Z")

</div>

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?

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/3/c3accb318f1b3dcd759bcf0162f5d85febbf446c.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [January 28, 2023, 10:43pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/8 "2023-01-28T22:43:53Z")

</div>

> [@AJBerera](#):
>
> . It doesn't seem to give me the last column name as Stopped, any idea how to fix 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:

```auto
SET SESSION group_concat_max_len = 1000000;

```

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [January 29, 2023, 4:34am UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/9 "2023-01-29T04:34:12Z")

</div>

> [@AJBerera](#):
>
> it doesn't work as a named query in Ignition, any idea how to get around that?

Save it in the database as a stored procedure and then [Call the stored procedure from Ignition](https://docs.inductiveautomation.com/display/DOC81/SQL+Stored+Procedures).

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [January 30, 2023, 3:34pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/11 "2023-01-30T15:34:13Z")

</div>

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.

EDIT:  
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.

---

<div class="post-metadata">

**Author:** ![AJBerera](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@AJBerera](https://forum.inductiveautomation.com/u/AJBerera)\
**Post date:** [January 30, 2023, 5:23pm UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/12 "2023-01-30T17:23:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [January 31, 2023, 4:39am UTC](https://forum.inductiveautomation.com/t/select-variable-number-of-columns-with-mysql/68885/13 "2023-01-31T04:39:13Z")

</div>

> [@AJBerera](#):
>
> I am not selecting on a single machine

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

```auto
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)');

```