[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2008-10-03
Medium Priority
Last Modified: 2013-12-18
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?
Question by:dajm
  • 3
  • 2

Accepted Solution

Dorfs earned 500 total points
ID: 22632499
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

Author Comment

ID: 22632692
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)

Expert Comment

ID: 22633010
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.

Author Comment

ID: 22649823
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;

Author Closing Comment

ID: 31502701
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.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question