SQL & PL/SQL

Hi,

I have searched the forum to try and find an example of the problem I am having but
have not had any luck finding anything. Some help would be appreciated.

I am currently using Oracle 9i.

The problem is that I have a query, returning results as shown below
and I want to remove as many null values as possible.

Goals , Team1 , Team2
1 , Jim , NULL
1 , John , NULL
1 , NULL , Bob
2 , Harry , NULL
2 , NULL , Steve
3 , Jill ,NULL
3 , NULL , Penny
3 , NULL , Sally

The Goals column is numeric, is not unique and is always filled.
The Team1 column contains a name or is null.
The Team2 column contains a name or is null.
Either Team1 has a non null value or Team2 has a non null value.
They cannot both be filled or both be blank.


The result set I would like is an order on Goals, where we collapse the results as much as possible.
So that if a non null Team1 row and Team2 row exist for a given number of goals, they are shown on the same row.
We repeat this till all results are shown for a given number of goals.
If there are more results in Team1 than Team2 say for a given goals scored, we show the team1 result and null for team2 and vice versa.

If the Team1 and Team2 values can be sorted on name within the same goals scored, this would be great as well


To visualise this, the table would look like: -

Goals , Team1 , Team2
1 , Jim , Bob
1 , John , NULL
2 , Harry , Steve
3 , Jill , Penny
3 , NULL , Sally

Thanks for the help,

Rich