Oracle - Excel view

Question

Im using Oracle XE11g and Excel 2007. I've created a View in SQL Developer which selects fields from multiple tables. The intention is to allow Excel to import this View using ODBC. The problem is that the view isn't giving the data the way I want it in Excel

If I use my created view into Excel I get something like this:

personname - testname    - time     - objectname   - result - date
Edward     - RunningTest - 15:22:01 - Speed (km/h) - 12     - 24-04-2012
Edward     - RunningTest - 15:22:01 - Heart Rate   - 98     - 24-04-2012
Edward     - RunningTest - 15:22:01 - Power        - 50     - 24-04-2012
Edward     - RunningTest - 15:22:02 - Speed (km/h) - 13     - 24-04-2012
Edward     - RunningTest - 15:22:02 - Heart Rate   - 99     - 24-04-2012
Edward     - RunningTest - 15:22:02 - Power        - 12     - 24-04-2012
Edward     - RunningTest - 15:22:03 - Speed (km/h) - 12     - 24-04-2012
Edward     - RunningTest - 15:22:03 - Heart Rate   - 100    - 24-04-2012
Edward     - RunningTest - 15:22:03 - Power        - 12     - 24-04-2012

And this is the query of the view I use:

select psn.naam personname
, tst.name testname
, tms.seconds time
, obj.name objectname
, trt.result result
, dtm.days date
from persons psn
, results rst
, times tms
, tests tst
, objects obj
, dates dts
where psn.id=trt.persons_id
and obj.id=trt.objects_id
and obj.tests_id=tst.id
and trt.date_id=dts.id
and rst.times_id=tms.id
and tst.name='RunningTest'

Please note that the query is translated into English fieldnames so there might be some errors in it.

The way I'd like to have the data represented in Excel is like this:

Personname
Edward

Date
24-04-2012

    Time     - Speed (km/h) - Heart Rate - Power
    15:22:01 - 12           - 98         - 50
    15:22:02 - 13           - 99         - 52
    15:22:03 - 12           - 100        - 51
    etc------------

Is there any way to build the view so it shows the data like above? Any help is appreciated.

Answer 1

You can kindly test and use existing answers and use below to insert end of line

Select name || chr(10) || chr(13) || date || chr(10) || chr(13)|| other select columns in your view

to achieve the desired results in Windows OS

Adopting Bob's answer with your desired results

SELECT PERSONNAME || chr(10) || chr(13) as PERSONNAME,
       DATE || chr(10) || chr(13) as DATE,
       chr(10) || chr(13) ||TESTNAME,
       OBJECTNAME,
       RESULT
  FROM (SELECT *
      FROM (SELECT psn.NAAM    PERSONNAME,
               tst.NAME    TESTNAME,
               tms.SECONDS TIME,
               obj.NAME    OBJECTNAME,
               rst.RESULT  RESULT,
               dts.DAYS    DATE
          FROM PERSONS psn,
               RESULTS rst,
               TIMES   tms,
               TESTS   tst,
               OBJECTS obj,
               DATES   dts
         WHERE psn.ID = rst.PERSONS_ID
           AND obj.ID = rst.OBJECTS_ID
           AND obj.TESTS_ID = tst.ID
           AND trt.DATE_ID = dts.ID
           AND rst.TIMES_ID = tms.ID
           AND tst.NAME = 'RunningTest') t PIVOT(SUM(RESULT) 
           FOR OBJECTNAME IN ('Speed (km/h)', 'Heart Rate', 'Power')));

Answer 2

The following PIVOT query should get close to what you want, but without the data to play with I'm not 100% sure - but it should provide a starting point for you to work from:

SELECT * FROM
  (SELECT * FROM
    (SELECT psn.NAAM PERSONNAME,
            tst.NAME TESTNAME,
            tms.SECONDS TIME,
            obj.NAME OBJECTNAME,
            rst.RESULT RESULT,
            dts.DAYS DATE
       FROM PERSONS psn,
            RESULTS rst,
            TIMES tms,
            TESTS tst,
            OBJECTS obj,
            DATES dts
       WHERE psn.ID = rst.PERSONS_ID AND
             obj.ID = rst.OBJECTS_ID AND
             obj.TESTS_ID = tst.ID AND
             trt.DATE_ID = dts.ID AND
             rst.TIMES_ID = tms.ID AND
             tst.NAME = 'RunningTest') t
   PIVOT(SUM(RESULT)
         FOR OBJECTNAME IN ('Speed (km/h)', 'Heart Rate', 'Power')));

Share and enjoy.

Answer 3

You have two problems. The first is that your query isn't providing the data that you need in Excel. This is a classic problem arising from using key-value pairs instead of a proper schema.

Here is one possible solution:

select psn.naam personname 
      , tst.name testname 
      , tms.seconds time 
      , rst1.result speed
      , rst2.result heartrate
      , rst3.result power
      , dtm.days date 
from persons psn 
     , results trt1 
     , results trt2 
     , results trt3 
     , times tms 
     , tests tst
     , objects obj 
     , dates dts 
where psn.id=trt.persons_id 
and obj.tests_id=tst.id 
and trt1.date_id=dts.id 
and trt2.date_id=dts.id 
and trt3.date_id=dts.id 
and trt1.times_id=tms.id 
and trt2.times_id=tms.id 
and trt3.times_id=tms.id 
and  ( ( obj.name = 'Speed (km/h)' and obj.id=trt1.objects_id )
       or  ( obj.name = 'Heart Rate' and obj.id=trt2.objects_id )
       or   ( obj.name = 'Power' and obj.id=trt3.objects_id )
      )
 and tst.name='RunningTest' 

Caveat: your aliasing was mangled in translation so this may well not work, but the basic principle is you need three instances of the RESULTS table, one for each disctinct attribute you're recalling from the OBJECTNAMES table.

The second problem is that your layout doesn't match the result set. This is another classic problem, this time associated with using the wrong tool for the job. A spreadsheet is not really a GUI presentation tool. The great strengths of Excel is working with matrices of columns and rows. So normally it's a good fit for working with databases, because queries return columns and rows.

You're in a pickle because you want to display different some columns once and some coumns multiple times. There are a number of different ways of working with this.

1. Use an inline cursor expression to return a jagged resultset. Although frankly I don't know whether ODBC supports this and whether Excel will be able to render it.
2. Use two queries, one to return the "master data" - PersonName, Date - and one to retrieve the details - everything else.
3. Use your existing query to retrieve the full result set into one worksheet but present the user with a differnet worksheet which uses references to achieve the layout you require.
4. Use a proper GUI development tool to build your client application. There are any number of them on the market. Apex might be a good choice: find out more.