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

Posted on 2008-10-03
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 125 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
run sql script from putty 4 35
SQL Help 27 43
sql server insert 12 30
SQL Syntax: How to force case sensitive query? 2 24
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

776 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