|
create table a(docid number,pid number,pname varchar2(10));
create table b(pid number,pcno number) ;
insert into a values(100,1,'aa');
insert into a values(100,2,'bb');
insert into a values(200,3,'cc');
insert into b values(1,320122);
insert into b values(2,320123);
insert into b values(3,320124);
select docid,max(decode(t,1,pid)),max(decode(t,1,pname)),max(decode(t,2,pid)),max(decode(t,2,pname)) from
(select a.docid,a.pid,a.pname, row_number() over(partition by a.docid order by a.pid) t from A,B where A.pid=B.pid)
group by docid; |
|