sql problem

I am running into a problem at work that I have not come up with a good solution for yet (w/o using pl/sql a record at time). First off, this is 10g. What I have is 2 keyed tables that can have a different number of rows per key. The particular application here is basically an account/customer system. So, for example, Table1 could have 2 rows per key, while Table2 could have 3 rows per key. What I need to do is pivot the data to a row, but the catch here is that I only want it pivoted to a max of 2 columns. I have used the standard pivot stuff with
select key, max(decode(.....)....., but it requires a group by clause, which then pivots to 1 row. I need to be able to pivot to multiple rows. If you dont group by (and thus no max(decode), you get pivoted data, but a different column on each row. I've also tried using full outer join, but in that case, I got the data pivoted, but if table 1 only had 1 row and table 2 had 2 rows, I got back 2 rows with the data from the first row duplicated on the second row. The 2 tables have basically the same structure.

The other requirement I have is that if there is a common name in the name field, I need that displayed on the same row. For example, if table1 had key1, John and table2 had key1, rows Mary and John, I need the John rows together, followed by a Mary row.

I've included some scripts to put a very simplified sample out there. Also, have what each table looks like and what I'm trying to get to. Any ideas would be great.

create table table1
(

    key number(),
    name varchar2(30),
);

create table table2
(

    key number(),
    name varchar2(30),
);

insert into table1 (1, 'John')
insert into table2 (1, 'Mary')
insert into table2 (1, 'John')
insert into table1 (2, 'Bob')
insert into table1 (2, 'Mark')
insert into table1 (2,'George')
insert into table2 (2,'Mark')
insert into table2 (2,'Steve')
insert into table1 (3,'Susan')
insert into table1 (3,'Sheila')
insert into table2 (3,'Jenna')
insert into table2 (3,'Martha')

What I need is the following:

Key             Table1Val              Table2Val
-----------        --------------              --------------
1                 John                      John
1                                              Mary
2                 Bob
2                 Mark                      Mark
2                 George
Steve
3                 Susan                    Jenna
3                 Sheila                    Martha

So basically need to be joined by the name so they end up on same row. Doesn't matter what order the names are in, as long as the ones that are the same are on the same row. So the Bob and George row could be reversed here (nor does it matter if Bob ends up in the same row as Steve and George was on the row by itself)

Hope this makes some sense.

Many thanks ! Received on Wed Mar 19 2008 - 22:47:32 CDT