Oracle行列转换问题2~

Question

A表： NAME  TYPE  TIME        B表： ID  TYPE_NAME 
      SAM    1      3                1    病假 
      SAM    2      4                2    事假 
      SAM    3      2                3    出差 
      Bob    1      2 
      Bob    2      5 
      Bob    3      1 
      Rose    1      2 
      Rose    2      0 
      Rose    3      3 A type 和 B表 ID对应 
要求 通过行列转换查询 A,B表 得到以下格式 C表：
  NAME  病假  事假  出差 
  SAM    3      4    2 
  Bob    2      5    1 
  Rose   2      0    3B表中type_name 数量不定，也可能有更多类型 比如休假，缺勤等
所以要求使用游标读出B表与A表关联
在进行行列转换得到C表，B表TYPE_NAME有几个 则 C表就显示多少个字段

Answer 1

不是固定的，动态SQL拼接，以前经常写。
现在都转前台了，行列转换应该由应用程序做，数据库只负责取原始数据，否则影响性能。

Answer 2

下面给个类似实例，供楼主参考：
/*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次 
A1 2 C1 C1         20         123 
A1 2 C1 C2         30         111 
A1 2 C1 C2         20         222 
A1 2 C1 C3         10         211 
A2 3 C4 C1         40         321 
A2 3 C4 C4         50         222 
A2 3 C4 C4         60         333 
A2 3 C4 C5         70         223 
我需要把上面的查询结果转换为下面的。
物料 需要数量 需要仓库 C1 C2 C3 C4 C5 
A1 2 C1 20 50 10 0 0 
A2 3 C4 40 0 0 110 70 
*/ 
------------建表
CREATE TABLE TEST
(
WL VARCHAR2(10),
XYSL INTEGER,
XYCK VARCHAR2(10),
XCLCK VARCHAR2(10),
XCLCKSL INTEGER,
PC INTEGER
);
------------ 第一部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' ,        20,         123); 
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        30,         111); 
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        20,         222); 
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' ,        10,         211); 
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' ,        40,         321); 
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        50,         222); 
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        60,         333); 
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' ,        70,         223);
COMMIT;
--------------------  动态生成结果表
DECLARE
  V_SQL VARCHAR2(2000);
  CURSOR CURSOR_1 IS
    SELECT DISTINCT T.XCLCK
      FROM TEST T
     ORDER BY XCLCK; 
BEGIN
  V_SQL := 'SELECT WL,XYSL,XYCK';  FOR V_XCLCK IN CURSOR_1
  LOOP
    V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||
             ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;
  END LOOP;
  V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE TABLE RESULT  AS '||  V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
--------------- 结果
SELECT * FROM RESULT T;
WL                                            XYSL XYCK               C1         C2         C3         C4         C5
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
A1                                               2 C1                 20         50         10          0          0
A2                                               3 C4                 40          0          0        110         70
--------------- 删除结果表
DROP TABLE RESULT; 
------------ 第二部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' ,        20,         124); 
INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' ,        30,         121); 
INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' ,        20,         322); 
COMMIT;---------  从新运行 动态生成结果表 语句块
--------------- 结果
SELECT * FROM RESULT T;
WL     XYSL XYCK          C1       C2         C3         C4         C5         C6         C7         C8
----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------
A1        2 C1            20       50         10          0          0         20          0          0
A2        2 C1             0        0          0          0          0          0         30          0
A2        3 C4            40        0          0        110         70          0          0          0
A3        2 C1             0        0          0          0          0          0          0         20
--------------- 删除结果表
DROP TABLE RESULT;   

Answer 3

declare
v_sql VARCHAR2(2000);
--c_type b.type_name%type;
cursor c_a is select type_name from B;
begin
  v_sql:='select a.name,';
     -- open c_a;
     for c_type in c_a 
      --fetch c_a into c_type
      loop
           v_sql := v_sql ||'sum(decode(b.type_name,''' ||c_type.type_name||
           ''',a.time)) as ' || c_type;
           end loop;           
   v_sql:=v_sql||' from A a,B b where a.type=b.id group by a.name;';            v_sql := 'CREATE TABLE RESULT  AS '||v_sql;       
    EXECUTE IMMEDIATE v_sql;       
end;
报调用'||'参数个数或类型错误

Answer 4

试试这个
declare 
v_sql VARCHAR2(2000); 
c_type b.type_name%type; 
cursor c_a is select type_name from B; 
begin 
  v_sql:='select a.name'; 
  open c_a; 
  fetch c_a into c_type ;
  while c_a %found loop 
    v_sql := v_sql ||',sum(decode(b.type_name,'''||c_type||''',a.time)) as ' || c_type;
    fetch c_a into c_type ; 
  end loop;          
  v_sql:=v_sql||' from A a,B b where a.type=b.id group by a.name';            v_sql := 'CREATE TABLE RESULT  AS '||v_sql;      
    EXECUTE IMMEDIATE v_sql;      
end;