Could use some more info, but assuming only one local and one
permanent address, here's a simple solution:
create table t_name (ID number, name varchar2(20));
create table t_address (ID number, address varchar2(20), add_type
varchar2(20));
insert into t_name values( 1,'Fred');
insert into t_name values( 2,'Barney');
insert into t_name values( 3,'Wilma');
insert into t_address values (1,'1 local st', 'LOCAL');
insert into t_address values (1,'1 perm st', 'PERMANENT');
insert into t_address values (2,'2 local st', 'LOCAL');
insert into t_address values (3,'3 local st', 'LOCAL');
insert into t_address values (3,'3 perm st', 'PERMANENT');
create table merged_ab as (
select a.id
,a.name
,(select address from t_address b where a.id = b.id and
b.add_type = 'LOCAL') local_address
,(select address from t_address b where a.id = b.id and
b.add_type = 'PERMANENT') perm_address
from t_name a)
select * from merged_ab order by 1
SQL> select * from merged_ab order by 1;
ID NAME LOCAL_ADDRESS PERM_ADDRESS
---------- -------------------- --------------------
--------------------
1 Fred 1 local st 1 perm st
2 Barney 2 local st
3 Wilma 3 local st 3 perm st
On Jul 16, 12:50 pm, Michael <[email protected]> wrote:
> I have two data tables (one with names, and another with multiple
> addresses for that name)
>
> NAME
> +ID(PK)
> NAME
>
> ADDRESS
> +ID
> ADDRESS
> TYPE (LOCAL and PERMENANT)
>
> I need to merge this into a single table
>
> NAME_MERGE
> +ID
> NAME
> LOCAL_ADDRESS
> PERMANTANT_ADDRESS
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
