Streaming Data To Spare Matrix Transform Design Help

From: Joe Compton <jinianc_at_concentric.net>
Date: 17 Apr 1999 10:07:30 PDT
Message-ID: <3718C1D1.B838EA5D@concentric.net>

Hi,

I have a requirement where I need to transform streaming data into a sparse matrix. I am trying to see how best to approach the problem - Can I do this all in SQL or in PL/SQL which gives me a couple of different approaches as well: A predefined temp table, but fixed number of columns for characteristic; A data structure; Object table/view, Dynamic SQL to create the table; etc.?. All ideas are welcome at this point.

Background:
This is a basic lab data collection problem where data samples are stored in the streaming data table, The simplified format :

Test, Sample #, Test Characteristic Name, Value The tests are performed on different materials, so for or each unique test there are different # of characteristics, which may change over time. For each characteristic within a test, it may be sampled at different rates. I need to transform the data into a matrix that has a column for each unique characteristic with the value. In Excel, this is done through a pivot table.

These extracted results are to be fed into a statistical analysis program that needs the sparse matrix format. I can't change the database design or the layout structure of the query results. It would be best if this was a stored procedure that could be passed a parameter for which test set to pull back.

I am working on a stored procedure (using Dynamic SQL approach) that does the following logic:

     find all of the unique characteristic.
     Create a table with 1 column/characteristic
     select all of the data values for the test
         for each row, determine the column to insert into
     select * from the new table
     drop the table.

I have an issue with our DBO's about using Dynamic SQL package. The role that we have our user's connecting in doesn't have create/drop authority.

We are running 8.0.5 Enterprise. Running database server on NT Server (2-450 Pent. 1 Gig Ram). No other applications running on.

Sample Data View

Test Sample# Characteristic Value

100         1                PH                    7
100         1                % Sol                 23.67
100         1                effluent               .63
200         1                Temp                 45.6
200         1                % Sol                7
200         1                CL mg/l            .6
200         1                Diff %               16
100         2                PH                    7

... (multiple entries )

Sample Output View Desired:

Test        Sample #     % Sol     effluent     PH
 100            1                 23.67     0.63         7
 100            2                 22.22
 100            3                                0.65       6.8
 100            4                               .68

Test             Sample #     % Sol     CL mg/l     Diff %     Temp
 200                 1                 7             0.6
16        45.6
 200                 2
0.6                     46.3
 200                 3                 7
16         45.6

Received on Sat Apr 17 1999 - 12:07:30 CDT