I was curious if the queryTagHistory function does the table pivot using a single pivot type SQL query or does the function make numerous queries and then builds a pivot table itself? The reason I’m curious is because I’m creating a table similar to what you use in the SQLTags Historian, in the past I’ve used a pivot query to return the data, but now I’ve been going back an forth as to whether I should pivot the data using a query or to make numerous queries (less than 10 in most cases) and then create a pivot table in Python. Thoughts?
No, there are no pivot queries done, but still only one data query. All of the processing happens in code. As the values come in (queried for all tag ids, ordered by time ascending) they are shuffled into different columns based on the tag id. Each column knows about the current “window size” (time covered per row), so each column keeps track of its row values, given the aggregation mode. As time goes forward, completed rows are written to the resultset.
That’s just a rough overview of what the system’s doing, there are a few other particularities here and there. Ultimately, you could do something in python, or perhaps in a stored procedure. I guess the real trick is how you want to deal with lining up times/windows.