Ver. 7.9.4 Reports Table - Display Columns from same db table entry as separate rows

Ignition
1

Below is my SELECT UNION Query of the same table.
It is functioning as we wanted but I have to believe there is a better or cleaner way to get the same result. We want to display columns from the same table entry as separate rows.

SELECT task1 AS Job FROM prevent WHERE task1 != "" AND eq = ? AND id = ?
UNION
SELECT task2 AS Job FROM prevent WHERE task2 != "" AND eq = ? AND id = ?
UNION
SELECT task3 AS Job FROM prevent WHERE task3 != "" AND eq = ? AND id = ?

This is an example of the table we are wanting to display.

image

2

The exact way to do this depends pretty heavily on your SQL flavor. I’d start googling around <your sql brand> PIVOT or <your SQL brand> transpose.

3

We are using MySQL 5.6

4

MySQL doesn’t have PIVOT, so you’re actually using the best way if you want to keep it in a single query.

Personally, I would have done this in a script.

eqValue = 1
idValue = 1

query = "SELECT task1, task2, task3 FROM prevent WHERE eq = ? and id = ?"

result = system.db.runPrepQuery(query, [eqValue, idValue] , 'dbConnection') 

data=[]

for row in result:
	for col in row:
		if col != '':
			data.append([col])
			
dataOut = system.dataset.toDataSet(['Job'], data)
1 Like
5

How could I change my database design to improve a query like the one above?
Here is how my current data is being stored.

SELECT_UNION_sameTable2
SELECT_UNION_sameTable2744×72 1.41 KB

6

I would tend to have a separate table for the tasks so that each task has an ID, then this table would have a row for each task, where the task ID would change.

Then you wouldn't have to do any extra work, just an inner join with the Task table to get the task description based upon the id. Each task would then return a row from a single SELECT statement.

1 Like
7

You would be looking at storing it in a block.
2020-01-29_10-57-02
Your query would then be something like:

SELECT value FROM prevent
WHERE eq = ? and id = ? and param in ('task1', 'task2', 'task3')

On the other hand, you could improve performance on what you have by using UNION ALL instead of UNION. UNION ALL does not use an index in its temporary tables and also doesn't use DISTINCT.

UNION by itself is short for UNION DISTINCT, which skips duplicate rows, and is a bit more expensive to use.

2 Likes