# SQL to PowerTable

**URL:** <https://forum.inductiveautomation.com/t/sql-to-powertable/58150>\
**Category:** Ignition\
**Created:** [April 7, 2022, 9:27am UTC](https://forum.inductiveautomation.com/t/sql-to-powertable/58150 "2022-04-07T09:27:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gediminas.labutis](https://avatars.discourse-cdn.com/v4/letter/g/f6c823/32.png) [@gediminas.labutis](https://forum.inductiveautomation.com/u/gediminas.labutis)\
**Post date:** [April 7, 2022, 9:27am UTC](https://forum.inductiveautomation.com/t/sql-to-powertable/58150/1 "2022-04-07T09:27:53Z")

</div>

Could you point me to the right direction on how to fill power table with data from database. I can do that with simple SQL commands if single row in database corresponds to single row in table.  
But now I have a row in database which must be displayed across multiple rows in table.  
Each row corresponds to one production batch. And I want to display batch data.

Simplified version (there is ~200 columns) of row (a list of columns):  
BATCH\_ID, COMPONENT\_1\_NAME, COMPONENT\_1\_TARGET\_KG, COMPONENT\_1\_ACTUAL\_KG, COMPONENT2\_NAME, COMPONENT\_2\_TARGET\_KG, COMPONENT\_2\_ACTUAL\_KG

I need it to display in table like that (row by row):  
COMP1: NAME, TARGET\_KG, ACTUAL\_KG  
COMP2: NAME, TARGET\_KG, ACTUAL\_KG

---

<div class="post-metadata">

**Author:** ![jonathan.taylor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jonathan.taylor/32/4048_2.png) [@jonathan.taylor](https://forum.inductiveautomation.com/u/jonathan.taylor)\
**Post date:** [April 7, 2022, 9:31am UTC](https://forum.inductiveautomation.com/t/sql-to-powertable/58150/2 "2022-04-07T09:31:16Z")

</div>

Create a named query to retrieve the data

Use that named query as the data binding

Assuming you’re talking about Vision - use the power table customiser to hide columns you don’t need, and rename columns.

---

<div class="post-metadata">

**Author:** ![gediminas.labutis](https://avatars.discourse-cdn.com/v4/letter/g/f6c823/32.png) [@gediminas.labutis](https://forum.inductiveautomation.com/u/gediminas.labutis)\
**Post date:** [April 7, 2022, 11:01am UTC](https://forum.inductiveautomation.com/t/sql-to-powertable/58150/3 "2022-04-07T11:01:11Z")

</div>

So if I create SELECT query it returns data as a single row and binding that named query  
displays single line in power table.  
I need query which would return data formatted as two dimensional table and directly bind it to table or somehow format that data in table script (?)

---

<div class="post-metadata">

**Author:** ![jonathan.taylor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jonathan.taylor/32/4048_2.png) [@jonathan.taylor](https://forum.inductiveautomation.com/u/jonathan.taylor)\
**Post date:** [April 7, 2022, 11:04am UTC](https://forum.inductiveautomation.com/t/sql-to-powertable/58150/4 "2022-04-07T11:04:54Z")

</div>

It would be useful to see your query, and some potential rows of data. Unless there’s only one row of data in the database you should be getting more.

---

<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:** [April 7, 2022, 11:57am UTC](https://forum.inductiveautomation.com/t/sql-to-powertable/58150/5 "2022-04-07T11:57:18Z")

</div>

This looks to be a wide to tall format issue. Which means that you will need to do some scripting. As long as the db columns are consistently named (e.g. COMPONENT\_X\_NAME, it should be fairly simple to iterate through it.

mocked up named query:

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

Add a custom property to the table. I used ‘query’ and bound it to the named query:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/c/dcc3625c7f4436b4546af535b6ba33934ca724b5.png)  
Use a propertyChange script to transform the data:

```python
if event.propertyName == 'query':
	baseColNames = ['component_{}_name', 'component_{}_target_kg', 'component_{}_actual_kg']
	
	dataIn = system.dataset.toPyDataSet(system.db.runNamedQuery('Query', {}))
	
	headers = ['Comp', 'Name', 'Target', 'Actual'] 
	data = []
	for i in xrange(5):
		newRow = [i+1]
		for col in baseColNames:
			newRow.append(dataIn[0][col.format(i+1)])
		data.append(newRow)
	
	event.source.data = system.dataset.toDataSet(headers, data)

```