3,491
社区成员
发帖
与我相关
我的任务
分享
select 物料,需要数量,需要仓库,
sum(decode(现存量仓库,'C1',现存量仓库数量,0)) as C1,
sum(decode(现存量仓库,'C2',现存量仓库数量,0)) as C2,
sum(decode(现存量仓库,'C3',现存量仓库数量,0)) as C3,
sum(decode(现存量仓库,'C4',现存量仓库数量,0)) as C4,
sum(decode(现存量仓库,'C5',现存量仓库数量,0)) as C5
from mytab
group by 物料,需要数量,需要仓库
FUNCTION getsql RETURN varchar2 IS
v_ck varchar2(60);
str varchar2(4000);
cursor bh_list is select distinct 现存量仓库 from mytab order by 现存量仓库 ;
begin
open bh_list;
fetch bh_list into v_ck;
while bh_list %found loop
str:=str||',
sum(decode(现存量仓库,'''||v_ck||''',现存量仓库数量,0)) as '||v_ck;
fetch bh_list into v_ck;
end loop;
close bh_list;
str:='select 物料,需要数量,需要仓库'||str||'from mytab
group by 物料,需要数量,需要仓库';
return str;
end;
------------建表
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;