I have a query that is filling a table. Can the table be made to display the data top to bottom or one column with many rows, instead of left to right one row with many columns?
I think you could use a pivot function in your sql query.
Do you know if a pivot function will work with the built in IA table component?
You either have to find a database function to flip the results or flip them manually in Python scripting. Which database are you using?
My SQL
Well, it looks like you either have to create a stored procedure in MySQL to perform that function or use Python scripting in Ignition. In either case you have to get the data, loop through the rows and create a new result set out of it.
Travis, can you give us an example of doing it in python?
for example, we have a table with two columns of data, one being apples and one being oranges, along with a timestamp column. Lets say that we want apples to be on one row and oranges on the next, and then have columns for each day of the month that show the amount of apples/oranges for each day.
Sure, here is an example. Table 1 has three columns: Apples, Oranges, Timestamp. The following script will loop through the dataset creating a new one that is flipped:[code]data = system.dataset.toPyDataSet(event.source.parent.getComponent(‘Table 2’).data)
newData =
header = [“Label”]
col1Data = [“Apples”]
col2Data = [“Oranges”]
for row in data:
label = system.db.dateFormat(row[“Timestamp”], “yyyy-MM-dd”)
header.append(label)
col1Data.append(row[“Apples”])
col2Data.append(row[“Oranges”])
newData.append(col1Data)
newData.append(col2Data)
event.source.parent.getComponent(‘Table 3’).data = system.dataset.toDataSet(header, newData)[/code]Here is a picture:
I have also attached a window for you to look at .Convert.vwin (10.6 KB)Hope this helps.
sweet!