两张表合成一个视图，请高手帮忙最好能通过一个SQL语句实现

Question

现在有如下两张表分别为: 
表A   
      ID           Name 
      1            张三 
       2            李四 
表B 
      ID        XMMC       CODE      RYID 
      1         A项目一     XMONE       1 
      2         A项目二     XMTWO       1 
      3         A项目三     XMTHREE     1 
      4         B项目一     XMONE       2 要求一条SQL语句实现如下结果 表C 
    ID       NAME     XMONE       XMTWO       XMTHREE 
    1        张三       A项目一     A项目二     A项目三 
    2         李四       B项目一       [空]           [空] 请各位大侠帮忙哈,   谢谢!

Answer 1

参考一下
--测试数据
create table t (XH varchar2(10), DDATE date, SXF int);
insert into t
select 1,sysdate,10 from dual union all
select 1,sysdate+1,14 from dual union all
select 1,sysdate+2,23 from dual union all
select 2,sysdate,21 from dual union all
select 2,sysdate+1,24 from dual union all
select 3,sysdate,13 from dual union all
select 3,sysdate+1,22 from dual;
--
create or replace package sp_test
is
type ResultData is ref cursor;
procedure getRstData( rst out ResultData);
end sp_test;
/
create or replace package body sp_test
is
procedure getRstData( rst out ResultData)
is
begin
declare
cursor cur is select distinct (DDATE) from t;
tmp_ddate date;
str varchar2(4000);
begin
str:='select xh';
open cur;
loop
fetch cur into tmp_ddate;
exit when cur%notfound;
str:=str||',sum(decode(to_char(ddate,''yyyymmdd''),'||chr(39)||to_char(tmp_ddate,'yyyymmdd')||chr(39)||',sxf,0)) "'||to_char(tmp_ddate,'yyyymmdd')||'"';
end loop;
str:=str||' from t group by xh';
-- dbms_output.put_line(str);
close cur;
open rst for str;
end;
end;
end sp_test;
/--输出结果
1 10 14 23
2 21 24 0
3 13 22 0

Answer 2

总结一下关于行列转置的实现方法
1、固定列数的行列转换
如
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：select student, 
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;
　2、不定列行列转换
如
c1 c2
--- -----------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不这一类型的转换可以借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;select distinct c1 ,get_c2(c1) cc2 from table;
或者不用pl/sql，利用分析函数和 CONNECT_BY 实现：SELECT c1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (c2, ';')), 2) NAME
    FROM (SELECT c1, c2, rn, LEAD (rn) OVER (PARTITION BY c1 ORDER BY rn) rn1
            FROM (SELECT c1, c2, ROW_NUMBER () OVER (ORDER BY c2) rn
                    FROM t))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY c1;
3、列数不固定（交叉表行列转置）
这种是比较麻烦的一种，需要借助pl/sql：原始数据：
CLASS1     CALLDATE         CALLCOUNT
1          2005-08-08       40
1          2005-08-07       6
2          2005-08-08       77
3          2005-08-09       33
3          2005-08-08       9
3          2005-08-07       21转置后：
CALLDATE     CallCount1 CallCount2 CallCount3
------------ ---------- ---------- ----------
2005-08-09   0          0          33
2005-08-08   40         77         9
2005-08-07 　6 　　　　　0          21试验如下：
1）. 建立测试表和数据
CREATE TABLE t(
    class1 VARCHAR2(2 BYTE),
    calldate DATE,
    callcount INTEGER
);INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 40);INSERT INTO t(class1, calldate, callcount)
VALUES ('1', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 6);INSERT INTO t(class1, calldate, callcount)
VALUES ('2', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 77);INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/09/2005', 'MM/DD/YYYY'), 33);INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/08/2005', 'MM/DD/YYYY'), 9);INSERT INTO t(class1, calldate, callcount)
VALUES ('3', TO_DATE ('08/07/2005', 'MM/DD/YYYY'), 21); COMMIT ; 2）. 建立ref cursor准备输出结果集 
CREATE OR REPLACE PACKAGE pkg_getrecord
IS
    TYPE myrctype IS REF CURSOR;
END pkg_getrecord;
/3）. 建立动态sql交叉表函数，输出结果集 
CREATE OR REPLACE FUNCTION fn_rs
    RETURN pkg_getrecord.myrctype
IS
    s VARCHAR2 (4000); 
    CURSOR c1 IS
    SELECT ',sum(case when Class1='
            || class1
            || ' then CallCount else 0 end)'
            || ' "CallCount'
            || class1
            || '"' c2
    FROM t
    GROUP BY class1;
    r1 c1%ROWTYPE;
    list_cursor pkg_getrecord.myrctype;
BEGIN
    s := 'select CallDate ';
    OPEN c1;
    LOOP
        FETCH c1 INTO r1;
        EXIT WHEN c1%NOTFOUND;
        s := s || r1.c2;
    END LOOP;
    CLOSE c1;
    s := s || ' from T group by CallDate order by CallDate desc ';
    OPEN list_cursor FOR s;
    RETURN list_cursor;
END fn_rs;
/ 4）. 测试在sql plus下执行：
var results refcursor;
exec :results := fn_rs;
print results;
CALLDATE        CallCount1 CallCount2 CallCount3
--------------- ---------- ---------- ----------
2005-08-09      0          0          33
2005-08-08      40         77         9
2005-08-07      6          0          21

Answer 3

CREATE TABLE  a as (select 1 id ,'項目一' XMMC,'XMONE' CODE,1 RYID FROM DUAL
           UNION
           select 2 id ,'項目二' XMMC,'XMTWO' CODE,1 RYID FROM DUAL
           UNION
           select 3 id ,'項目三' XMMC,'XMTHREE' CODE,1 RYID FROM DUAL
           UNION
           select 4 id ,'項目一' XMMC,'XMONE' CODE,2 RYID FROM DUAL
           )
WITH B AS (SELECT 1 ID,'張三' NAME FROM DUAL
           UNION
           SELECT 2 ID,'李四' NAME FROM DUAL          
           )
SELECT C.RYID,B.NAME,MAX(DECODE(RN,1,XMMC,NULL)) XMONE,
                     MAX(DECODE(RN,2,XMMC,NULL)) XMTWO,
                     MAX(DECODE(RN,3,XMMC,NULL)) XMTHREE     
FROM (SELECT RYID,XMMC,CODE,ROW_NUMBER()OVER(PARTITION BY RYID ORDER BY RYID) RN FROM A) C,B
WHERE C.RYID=B.ID
GROUP BY C.RYID,B.NAME  
ORDER BY C.RYID     RYID NAME XMONE  XMTWO  XMTHREE
---------- ---- ------ ------ -------
         1 張三 項目一 項目二 項目三
          2 李四 項目一        
 

Answer 4

select A.ID,A.Name,
case when B.Code=XMONE then B.XMMC else '' end XMONE,
case when B.Code=XMTWO then B.XMMC else ''end XMTWO,
case when B.Code=XMTHREE then B.XMMC else ''end XMTHREE
from A
left join B on A.ID=B.XMTHREEE

Answer 5

这是动态列的交叉表查询问题，可以通过procedure建视图来解决。
关键在于动态生成decode语句，拼接查询列！

Answer 6

 这种问题用视图解决比较方便
--建表
 create table testa
 (
 id int,
 name varchar2(20)
 );
 create table testb
 (
 id int ,
 xmmc varchar2(20),
 code varchar2(20),
 ryid int
 );
 --原始数据
insert into testa values( 1,'张三' );
insert into testa values( 2,'李四 ' );
insert into testb values( 1,'A项目一','XMONE',1 );
insert into testb values( 2,'A项目二','XMTWO', 1 );
insert into testb values( 3,'A项目三','XMTHREE',1  );
insert into testb values( 4,'B项目一','XMONE',2  );
--创建视图
create or replace view v_one
as
select a.id,a.name,b.xmmc as xmone 
from testa a,testb b
where a.id= b.ryid and code='XMONE'create or replace view v_two
as
select a.id,a.name,b.xmmc as xmtwo 
from testa a,testb b
where a.id= b.ryid and code='XMTWO'create or replace view v_three
as
select a.id,a.name,b.xmmc as xmthree 
from testa a,testb b
where a.id= b.ryid and code='XMTHREE'
--生成报表
select a.*,b.xmtwo ,c.xmthree from v_one a,v_two b ,v_three c where a.id=b.id(+) and a.id=c.id(+)