In order to improve data storage, simplicity for adding new reports (export format), and maintainability, I have started working on a new layout that is greatly simplified. The problem that I have however is getting data that is in rows to transform into columns. The attached images should help illustrate what I am aiming for.
The following image shows a first draft if you will of my envisioned table structure.
When queried, I want the point name, of which there are many, to become a column header and have the associated value become the row data. In the existing database's form, what I am asking above is achieved by creating a table that has each PointName as a column and the rows consist of CollectionValues.
This query produces the next image:
SELECT Points.PointName, Values.CollectionValue, Values.TimeStamp FROM (Collection INNER JOIN Points ON Collection.CollectionID = Points.CollectionID) INNER JOIN [Values] ON Points.ID = Values.PID WHERE Collection.CollectionName = 'Test Collection 2';
The end result I want would look like this:
testpoint4 | testpoint5 | testpoint6 | TimeStamp ------------------------------------------------- 16 45 25 35 35 46 0 23 34
While the TimeStamps do not align in the image above, they would in practice. Is what I am asking possible? A different layout is fine, I am just looking for a better way than individual tables for each layout when there are only two values I am storing and formatting is the only change. It seems that I should be able to use SQL to accomplish what these individual tables are.
I used MS Access above simply for convenience. The end result will use MySQL or MsSQL.