cross table

We have one table containing measured values which are obtained each minute from a data aquisition system. To reduce the amount of data (number of records) a threshold determines if data is written to the table or not. Detailed information about the data stored in this table can be queried from a sub-table using the ID field, but for this problem it is not relevant.
Because of data reduction its not guaranteed that all values will be written to the table after a timer elapses. The structure of the table looks like

            ID       NUMBER
            TSTAMP   DATE
            VALUE    NUMBER

Data looks like this:

       ID   TSTAMP        VALUE
       ------------------------
        9   15.04.,13:00  123.9
       31   15.04.,13:00   11.2
       39   15.04.,13:00  630.9
       44   15.04.,13:00   72.6
       31   15.04.,13:01   13.6
       39   15.04.,13:01  635.6
       44   15.04.,13:01   78.9
       31   15.04.,13:02   11.3
       .......

For displaying the data in a line graph I need a query result which looks like the next table where one record for each distinct timestamp should be generated. The query is restricted by selecting a range of timestamps and IDs. Values which were not saved at the corresponding timestamp should appear as NULL values. So the query should contain the values corresponding to timestamps as rows and the individual IDs as columns.

       TIMESTAMP      ID=9   ID2=31
       ----------------------------
       15.04.,13:00  123.9     11.2
       15.04.,13:01   NULL     13.6
       15.04.,13:02   NULL     11.3

Does anyone know how to achieve this result.

Thanx in advance

Martin

--

• Ing. Martin Gamperl
• MGS Messgeraetesteuerung - Softwareentwicklung
• Klosterwiesgasse 31/IV
• A-8010 GRAZ
• Tel. : +43-316-83 58 26
• Fax : +43-316-83 58 26
• Mobil : +43-664-40 03 032
• EMail : mgampi_at_ibm.net

  ---