Re: sql problem

"marfi95" <marfi95_at_yahoo.com> schreef in bericht news:0aff1652-033d-4cf9-af1a-0bc346802474_at_m3g2000hsc.googlegroups.com...
>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 !

Does not make any sense to me, what business requirement is this? What user would ask for this?

Shakespeare

Shakespeare Received on Thu Mar 20 2008 - 05:04:07 CDT