PI Admin

 

The following dynamic TSQL returns a PIVOTed table containing the average for the last half hour for all aliases in all modules under the branch “Plant”. If you'd like to adapt the code for your own MDB modules, replace references to 'Plant' with the name of your own module. The linked PIServer here is called PI, change if yours is called anything else.

DECLARE @columns VARCHAR(MAX)

DECLARE @expr_pivot    VARCHAR(MAX)

SELECT @columns = COALESCE(@columns + ',[' + cast([alias] as varchar) + ']',

'[' + cast([alias] as varchar)+ ']')

FROM OPENQUERY(PI, 'SELECT DISTINCT a.name alias FROM pimodule..pimoduleh m INNER JOIN pimodule..pialias a ON m.uid = a.moduleuid WHERE m.path LIKE ''\Plant\%''')

GROUP BY alias

SELECT @expr_pivot = 'SELECT Module, ' + @columns +

'FROM

(

SELECT al.module, al.alias, av.value

FROM OPENQUERY(PI, ''SELECT m.name Module, a.name alias, a.tag FROM pimodule..pimoduleh m INNER JOIN pimodule..pialias a ON m.uid = a.moduleuid WHERE m.path LIKE ''''\Plant\%'''''') al

INNER JOIN OPENQUERY(PI, ''SELECT tag, time, value FROM piarchive..piavg WHERE tag IN (SELECT a.tag FROM pimodule..pimoduleh m INNER JOIN pimodule..pialias a ON m.uid = a.moduleuid WHERE m.path LIKE ''''\Plant\%'''') AND time between ''''*-30m'''' AND ''''*'''''') av

ON al.tag = av.tag

) vals

PIVOT

(

SUM(value)

FOR alias IN

( ' + @columns + ' )

) AS pvt '

EXEC (@expr_pivot)

 

The code results in:

My module database looks like this: