multiple values query

[petzi]

Hi!

Can you please give me a hint for solving this problem:

I have a table with multiple values - e.g.

id |lastname| mother|father|child
007 Maier Klara
007 Maier Paul
007 Maier Fritzchen
008 Schmidt Anna
008 Schmidt Heinz
008 Schmidt Petra


and the result should look like this:

ID lastname mother father child
007 Maier Klara Paul Fritzchen
008 Schmidt Anna Heinz Petra

thanks for your help.
Petra.

[Rob Vollman]

Check out CONNECT BY, there might be a way with that.

[DaPi]

I think you have a fundamental design problem here:
With the table as it is you have NO WAY of ensuring that mother/father/child are retrieved in the required order.

To get the required layout, search in this forum (or AskTom) for "Pivot".

[gamyers]

I think you are suffering from formatting

If
id |lastname| mother|father|child
007 Maier Klara
007 Maier Paul
007 Maier Fritzchen
008 Schmidt Anna
008 Schmidt Heinz
008 Schmidt Petra

Is actually
id.....|lastname| mother|father|child
007..|Maier...|Klara..|......|.........|
007..|Maier...|.......|Paul..|.........|
007..|Maier...|.......|......|Fritzchen|
008..|Schmidt.|Anna...|......|.........|
008..|Schmidt.|.......|Heinz.|.........|
008..|Schmidt.|.......|......|Petra....|

Then
select id, max(mother), max(father), max(child)
....
group by id
should work

(Assuming each id only has one mother, father and child)

[WilliamR]

Using "view source" in the browser and interpreting the tab characters, it seems to be something like this, as gamyers suggested:

ID     Lastname   Mother    Father    Child
------ ---------- --------- --------- ------------
007    Maier      Klara
007    Maier                Paul
007    Maier                          Fritzchen
008    Schmidt    Anna
008    Schmidt                        Heinz
008    Schmidt                        Petra

It's a bit easier to read without the pipes and dots

[TimHall]

Hi.

I would definitely go with gamyers approach if the number of children were fixed. If they can be variable I would probably use some string aggregation to return the data like:

http://www.oracle-base.com/articles/...Techniques.php

Of course, there could be multiple mothers and fathers if you were talking about legal stuff, rather than biological

Cheers

Tim...

[petzi]

Thanks for your answers.

sorry, for the bad formatting, but it looks like WilliamR.

ID     Lastname   Mother    Father    Child
------ ---------- --------- --------- ------------
007    Maier      Klara
007    Maier                      Paul
007    Maier                                Fritzchen
008    Schmidt    Anna
008    Schmidt                 Heinz
008    Schmidt                               Petra 

But it's possible that there are more than 1 children.

This kind of table structure is made from an external software.
For a special report I need to restructure the records.

thx for the help.

[slimdave]

If you have multiple children then ...

select id, lastname,mother,father,child
from
(
select id,lastname,max(mother) over (id) mother,
max(father) over (id) father,child
from ...
)
where child is not null