# Ignition V7.9.16 Vision - Convert rows to columns

**URL:** <https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333>\
**Category:** Ignition\
**Tags:** vision, ignition79\
**Created:** [December 9, 2020, 11:59am UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333 "2020-12-09T11:59:32Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [December 9, 2020, 11:59am UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/1 "2020-12-09T11:59:32Z")

</div>

Sorry, if the topic title is not OK,…

I have a project in Ignition Vision, V7.9.16, where we are saving the energy (electricity, air) consumption, and good and bad pieces counters every hour to the tables in the PostgreSQL database.  
I made a view in the PostgreSQL database, which combines two tables (one for energy and one for pieces count). This is what I get:

 ![My_table](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/a/6a1fa50d3a58b957a04ccc1cbfd3c3397f2dcbb8.png)  
But our customer wants to have a ‘table’ like this:  
 ![Customer_table](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/3/831dc2c58d2eb1582ee7737210f589725f217e2a.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/e/9e4752ad98144457ba3250438680dbb961904edc.png)  
(this blue one is the original picture from our customer, I made mine in Excel with English)

Is it possible to do something like that with PostgreSQL?

---

<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:** [December 9, 2020, 12:39pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/2 "2020-12-09T12:39:08Z")

</div>

Pivoting flips rows to columns, but every DB I know will only source from one column to make the new columns (a grouping operation). I usually script this sort of thing. You might modify this function I use for simple tasks:

```auto
from com.inductiveautomation.ignition.common.util import DatasetBuilder

#----------
# Construct a dataset with columns from supplied datasets rows. The column
# names from the first dataset become the string values in the first column
# of the result. The first argument to the function is a sequence of names
# for the dataset arguments. The name string is used verbatim if the
# dataset has one row. Otherwise it is concatenated with the row number.
def flipData(names, *args):
	ds1 = args[0]
	firstcolumn = [x for x in ds1.columnNames]
	types = [jString]
	heads = ['_']
	columns = [firstcolumn]
	for dsn, ds in enumerate(args):
		collist = range(ds.columnCount)
		if ds.rowCount==0:
			heads.append(names[dsn])
			types.append(jObject)
			columns.append([None for x in firstcolumn])
		for rn in range(ds.rowCount):
			if rn==0:
				heads.append(names[dsn])
			else:
				heads.append("%s %d" % (names[dsn], 1+rn))
			types.append(jObject)
			row = [ds.getValueAt(rn, cn) for cn in collist]
			for i, x in enumerate(row):
				if isinstance(x, long):
					row[i] = jLong(x)
			columns.append(row)
	rows = zip(*tuple(columns))
	builder = DatasetBuilder.newBuilder().colNames(heads).colTypes(types)
	for row in rows:
		t = tuple(row)
		builder.addRow(*t)
	return builder.build()

```

---

<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:** [December 9, 2020, 4:43pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/3 "2020-12-09T16:43:46Z")

</div>

Hmm, I wonder if we could come up with a generic `system.dataset.pivot` function…

---

<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:** [December 9, 2020, 5:28pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/4 "2020-12-09T17:28:22Z")

</div>

Well, dataset pivoting itself already exists in my `view()` expression function. With a bit more functionality than you get with most DBs. But @zxcslo is after a more generic flipping of rows to columns, which my flipData() script does (not quite the way he wants, but close).

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [December 9, 2020, 6:33pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/5 "2020-12-09T18:33:11Z")

</div>

Thank you for your help, but I’m sorry, I really don’t understand, how to use that function with my table…  
Currently, there are 367 rows in the table…

 ![My_table2](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/e/ae20f822b4aecf22c49f32ce1bf78784d026067e.png)  
What are names and \*args for? What must I put into them?  
Example, maybe…?

---

<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:** [December 9, 2020, 7:21pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/6 "2020-12-09T19:21:54Z")

</div>

You won’t be able to use that function as-is. You’ll have to modify to your needs, as I mentioned. The _architecture_ of the function is an example of flipping rows into columns.

For context, that function takes one or more similar datasets, that are expected to have one or just a few rows, and turns those rows into columns. The `names` argument provides the column names, one per dataset. For the datasets with more than one row, the corresponding result columns get a sequential suffix.

The data you feed to such a flipping function will have to be grouped to have just one row for the one column you want out of it.

---

<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:** [December 9, 2020, 7:50pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/7 "2020-12-09T19:50:35Z")

</div>

As Phil implied, you would need to have your view be one row for each output column. The current view would need a bunch of ‘mini-pivots’, which are then rearranged (or smashed, if you prefer) into what you want.

If it’s not a straight flip, I’ll put the contents of the dataset into a dictionary, then construct the new dataset from the dictionary.

Untested, but it should illustrate.

```python

pointList = ['AIR', 'ELECTRICITY', 'GOOD', 'BAD']
shiftDict = {1:{}, 2:{}, 3:{}}
dataDict = {}

# Arrange dataset into dictionary
for i in datasetIn.rowCount:
	date = datasetIn.getValueAt(i,'DATE')
	shift = datasetIn.getValueAt(i,'SHIFT')
	# If date doesn't exits in the dictionary, add it.
	if date not in dataDict.keys():
		dataDict[date] = shiftDict.copy()
	for point in pointList:
		dataDict[date][shift][point] = datasetIn.getValueAt(i,point)

# Construct dataset
dateList = sorted(dataDict.keys())
dataOut = []

for shift in sorted(shiftDict.keys()):
	for point in pointList:
		newRow = []
		for date in dateList:
			newRow.append(dataDict[date][shift][point])
		dataOut.append(newRow)

datasetOut = system.dataset.toDataSet(dateList, dataOut)

```

---

<div class="post-metadata">

**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\
**Post date:** [December 10, 2020, 1:56pm UTC](https://forum.inductiveautomation.com/t/ignition-v7-9-16-vision-convert-rows-to-columns/41333/8 "2020-12-10T13:56:36Z")

</div>

Thank you all for your help. 👍  
I hope I’ll manage to do something useful with all this…