Re: combine rows dependent on match two columns

From: Ed Prochak <edprochak_at_gmail.com>
Date: Tue, 10 Mar 2009 11:08:03 -0700 (PDT)
Message-ID: <cbb8780f-e4aa-4d77-8ce8-34154242aa6f_at_h5g2000yqh.googlegroups.com>

On Mar 10, 8:14 am, NOVA <nova1..._at_gmail.com> wrote:
> Dear all,
>
> If I have table like this
>
> A             aa               B             bb
> 1              5
> 2              7
>                                   1              7
>                                   3              6
>
> I want to make the result like this
>
> A or B             aa               bb
> 1              5                7
> 2              7
> 3                                6
>
> A or B is a column has all data avaliable in both A and B
>
> help please

Logically, it seems you have two tables in one. Not exactly a design I would favor. but using that observation I suggest this:

drop table yourtable ;
create table yourtable
( A integer,
  aa integer,
  B integer,
  bb integer ) ;

insert into yourtable values (1,5,null,null);
insert into yourtable values (2,7,null,null);
insert into yourtable values (null,null,1,7);
insert into yourtable values (null,null,3,6);

select A, aa from yourtable where A is not null; select B, bb from yourtable where B is not null;

SELECT A, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA , (select B, bb from yourtable where B is not null ) yourB where yourA.A = yourB.B(+) ;

SELECT B, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA , (select B, bb from yourtable where B is not null ) yourB where yourA.A(+) = yourB.B;

SELECT A, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA , (select B, bb from yourtable where B is not null ) yourB where yourA.A = yourB.B(+)
UNION
SELECT B, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA , (select B, bb from yourtable where B is not null ) yourB where yourA.A(+) = yourB.B;

I'll let you decide if this is better (for whatever is your definition of better).
(though I sure hope this isn't homework!!!)

   Ed Received on Tue Mar 10 2009 - 13:08:03 CDT