Re: complex query

Torsten wrote:

> Hello,
> I have 4 tables like this:
>
> samples_water analyses
> ________________ ________________
> |sw_pid (PrimK) | |an_pid (PrimK) |
> |----------------| |----------------|
> |sw_name |---->|an_fid_sw (ForK)|
> |sw_id | |an_id |
> |sw_date | |an_date |
> |... | |... |
> ------------------ ------------------
> ||
> ||
> \/
> tracer_parameter an_tracer_water
> ________________ _________________
> |tp_pid (PrimK) | |atw_pid (PrimK) |
> |----------------| |-----------------|
> |tp_parameter | |atw_fid_an (ForK)|
> |tp_type |---->|atw_fid_tp (ForK)|
> ------------------ |atw_value |
> tp_parameter like: |atw_value_err |
> Tritium |... |
> Carbon-14... -------------------
>
>
> ...I hope that way the structure is described clearer.
>
> The analysed water samples are connected via the table analyses, which
> also points to other tables containing analyses parameters like
> chemistry...
>
> Now my table an_tracer_water has a different look like all other
> parameter tables. Because more then one tracer analysis can be derived
> from one sample but each atw_value having its own analyis-ID from the
> lab (an_id), making it unpossible to store the values for each tracer
> in columns instead of rows.
>
> What I want to:
> I need a view (especially retrieving it via php to an internet
> browser) like this:
> __________________________________________________________________
> sw_name|atw_value(tp_param.=Tritium)|atw_value(tp_param.=C-14)|...
> -------+----------------------------+-------------------------+---
> HuL678 | 5.3 | 72 |...
> ------------------------------------------------------------------
>
> If anyone has suggestions how to solve my problem...
>
> Thank you,
> Torsten

You are talking about a 'Pivot Query'. There's nothing built-in to Oracle to do this nicely, but if you go to the AskTom.oracle.com site then you'll get some clues - it usually involves lots of decodes and a partition statement.

-- 
Andy - Opinions are mine and do not necessarily reflect those of 
Advantica Ltd