sql problem
- From: marfi95 <marfi95@xxxxxxxxx>
- Date: Wed, 19 Mar 2008 20:47:32 -0700 (PDT)
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 !
.
- Follow-Ups:
- Re: sql problem
- From: Shakespeare
- Re: sql problem
- From: sybrandb
- Re: sql problem
- Prev by Date: Unsupported Recovery Technique......?
- Next by Date: Your resume is shortlisted - apply immediately | More than 85 TOP companies recruiting this week
- Previous by thread: Unsupported Recovery Technique......?
- Next by thread: Re: sql problem
- Index(es):
Relevant Pages
|
