How can I change the shape of a query's results

I am starting a project where an old system is being updated. The code is all VB (not .net) and we will be using ADODB recordsets etc (mostly becasuse the analysis software is certified and recoding it for say .net would need it to be recertified) An odbc connection to an oracle express 10g system for development and a full blown 9 for deployment.

Currently the software reads log files of the format:
     time stamp, value, value, value
     time stamp, value, value, value
The three values are for different devices, and their order is important, but is defined by another file (there are also more than 3, up to 20 in some cases). Each line has a diffeent timestamp, appearing in time order

We are now storing the raw data in a database table with:
   device id, timestamp, value
This table cannot be altered (and seems bad design to add multiple id and values as that would limit future work)

Most of my SQL up to this point has been pretty basic, so my initial suggestion of a series of:
   select value from table where device = deviceID
and then manually iterating through my set of recordsets to build a pseudo-logfile for the app to read (probably by building up a row and senting it to the target for the original read statement).

Is there a more elegant way of getting from a vertical table to a horizontal row?
ie from a table structure of:
      device id, timestamp, value
to a recordset of:
   timestamp, value, value,
where the value,value order would be defined into the query?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

DorfsConnect With a Mentor Commented:
you'd need to join the tables but it is gonne be a long query depending on the number of tables that need to be joined
dajmAuthor Commented:
Hi Dorfs.
Thanks for the suggestion. This is the way to go with multiple tables, but I only have 1 raw data table.

I have tried a join but although the shape seems right, the data isn't. I know there are 96 values per device but I'm getting much much more.

I will play about with the join to see if I can get it to work.

Clocking off early today (its my Birthday and my folks flew in for it, so I might not be back til Monday)
Happy birthday,

the way I see it its rather complex to do with a query but you basic want to get all the values from the devices on a certain timestamp the eazyest way to do this is with iterating and i'm fairly certain oracle will allow you to make some kind of function that does this for you, however I'm didn't work oracle enough to help you with making one.

The join makes every possible combination which than needs to be filtered with the" where" statements and in this case I really don't see it happening to get just one row that hasn't already been displayed and than display it. you could try to look into subquerys but I doubt its going to come up with something usefull.

I'd recommend looking into functions for oracle to basicly do the same thing your software is doing but than on the database level.
dajmAuthor Commented:
Hi Dorfs,
I got the join working correctly and its is definitely the answer to my questiion - it does give the right shape. The complexity of building the query isn't really a concern (the analysis may take 10's minutes so the overhead of building a complex query isn't a worry)
Sadly (for me) I don't think its the answer to my problem. I need to supply a timestamp to the analysis program and thought I would simply get the timestamp for the first device and its value and then simply the values for the other devices. I can then sort on the first device's timestamp to get its set in order, but cannot be sure the other joined devices are in the correct order...

For posterity,
instead of a series of
   select timstamp, value from table where device=id and time=range order by time
and then manually iterating over the set of results to build my array of values for a timestamp, the query:
   select t1.timestamp, t1.value, t2.value from table t1 inner join table t2 on t1.timestamp= t2.timestamp where t1.device=id1 and t2.device=id2 and trunc(t1.timestamp) = to_date('12-nov-06','dd-mon-yy') order by t1.timestamp;
dajmAuthor Commented:
Thanks for the quick response and apologies for my (albeit, only once a year) pre-occupation.
Sadly it looks like i will have to stick with my first idea and damn the elegance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.