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

**URL:** <https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [January 29, 2020, 12:40am UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701 "2020-01-29T00:40:54Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [January 29, 2020, 12:40am UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/1 "2020-01-29T00:40:54Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6a5115467f76a49e55d8c994169b46da5f7d1e5e.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 29, 2020, 12:46am UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/2 "2020-01-29T00:46:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [January 29, 2020, 12:48am UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/3 "2020-01-29T00:48:05Z")

</div>

We are using MySQL 5.6

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 29, 2020, 2:20pm UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/4 "2020-01-29T14:20:41Z")

</div>

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.

```python
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)
```

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [January 29, 2020, 3:39pm UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/5 "2020-01-29T15:39:00Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/cd09f7137344b8cde92188c99b19d09ed372daec.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 29, 2020, 4:02pm UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/6 "2020-01-29T16:02:42Z")

</div>

> [@paulm1](#):
>
> How could I change my database design to improve a query like the one above?  
> Here is how my current data is being stored.

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 29, 2020, 4:13pm UTC](https://forum.inductiveautomation.com/t/ver-7-9-4-reports-table-display-columns-from-same-db-table-entry-as-separate-rows/31701/7 "2020-01-29T16:13:01Z")

</div>

> [@paulm1](#):
>
> How could I change my database design to improve a query like the one above?

You would be looking at storing it in a block.  
 ![2020-01-29_10-57-02](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/72886efadc106b2b6b4f0c6a881eee079d9450ee.png)  
Your query would then be something like:

```sql
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.