How to display the last 3 values in a column horizontally

Ignition
,
1

I have the following named query:

SELECT TOP 3*
FROM
	(SELECT
	 schedOverTimemins as last5,
	 TableNum
	 FROM Tables_Data_East_V2
 	 WHERE  TableNum = 5
	) AS SUBQUERYB
	WHERE last5 IS NOT NULL

How would I display these three values in vision horizontally? I know it's probably a simple answer, but I am stuck. I can display them vertically using a table, but I need them to display horizontally.

Thanks

2

See if How to concatenate text from multiple rows into a single text string in SQL Server - Stack Overflow helps.

I've never done this and haven't tested it but I think it would be something like,

SELECT 
    LEFT(Last5, LEN(SUBQUERYC) - 1) AS 'Last5'
FROM
    (SELECT TOP 3*
        FROM
	    (SELECT
	      schedOverTimemins as last5,
	      TableNum
	      FROM Tables_Data_East_V2
 	      WHERE  TableNum = 5
	    ) AS SUBQUERYB
	    WHERE last5 IS NOT NULL
    ) SUBQUERYC

Alteratively you could use a script transform on your existing result.

3

Not in Vision.

4

Hmm this doesn't seem to work. It states that SUBQUERYC is not a column. Honestly it seems ridiculous that vision doesn't have the option to transpose rows and columns easily or even pull multiple rows into a string.

6

I like the idea of using a simple expression binding to solve this problem. For example, I could add a dataset custom property to the label, and bind the property to my query. See the example below:
image
image

Once I have the data in the label's custom property, I can display the data by applying an expression binding to the text property of the label:

try({Root Container.Label.data}[0,0],"")+ ", "
+try({Root Container.Label.data}[1,0],"")+ ", "
+try({Root Container.Label.data}[2,0],"")

image

The preceding expression produces the following result:
image

1 Like
7

Well, Vision may not have script transforms built into the binding window, but you can get the effect of that by using runScript() or my objectScript() in an expression binding. Such a script can perform whatever transpose operation you like. This solution predates Perspective:

You might also find the Pseudo-SQL PIVOT from my view() expression function useful.

{ objectScript() and view() are from my free Simulation Aids Module. }

2 Likes
8

groupConcat:
https://docs.inductiveautomation.com/display/DOC81/groupConcat
Works (on datasets) in ~every Ignition version you're likely to come across.

2 Likes