Is there a way to query PI AF using SQL to get the element attributes as columns?
Example, the elements have 2 attributes Name, Address. Can we get the below results
Element Name Address
====== ==== ======
1 A 123 W
2 B 256 E
Yes this is possible with the DataT schema. See
and associated sections on Table Valued Functions.
Some examples can be accessed via these steps:
Open PI SQL Commander.
Go to View > Solution Explorer.
Select PI SQL Query Compendium (Asset), then open the Queries > Transposed Data folder.
Double-click the file TransposedData.sql.
An example query is
SELECT eh.Name Element, tc.*
FROM [NuGreen].[Asset].[ElementHierarchy] eh
CROSS APPLY [NuGreen].[DataT].[TransposeSnapshot_Cooling Fan](eh.ElementID) tc
WHERE eh.Path LIKE N'\NuGreen\Little Rock\Extruding Process\%'
Please do note that this only works on Elements that use an ElementTemplate!
Yes, we always use templates for elements.
Awesome. Thank you Barry!
I will try that out.
I hava marked Barry's answer as correct as I believe this is the answer to your question.
As you get used to PI Square marking the correct answer is something you will probably be doing as well
Thanks for visiting us and let us know in case you have more questions!
I don’t see the DataT functions. Do I need to install anything else?
PISQL Commander 2012 – 1.3.15
PI AF Version – 22.214.171.12438
You should find them here:
Retrieving data ...