Ignition V7.9.16 Vision - Convert rows to columns

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:

But our customer wants to have a ‘table’ like this:

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

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:

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:
			columns.append([None for x in firstcolumn])
		for rn in range(ds.rowCount):
			if rn==0:
				heads.append("%s %d" % (names[dsn], 1+rn))
			row = [ds.getValueAt(rn, cn) for cn in collist]
			for i, x in enumerate(row):
				if isinstance(x, long):
					row[i] = jLong(x)
	rows = zip(*tuple(columns))
	builder = DatasetBuilder.newBuilder().colNames(heads).colTypes(types)
	for row in rows:
		t = tuple(row)
	return builder.build()

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


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

1 Like

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…

What are names and *args for? What must I put into them?
Example, maybe…?

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.

1 Like

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.

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:

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

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