oracle----------行转列(动态行转不定列)----测试通过(9i)
/*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次
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
*/
---------------------------------------------------------------建表
----------------判断表是否存在
declare num number;
begin
select count(1) into num from user_tables where table_name='TEST';
if num>0 then
execute immediate 'drop table TEST';
end if;
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;
--select * from test;
---------------------------------------------------------------行转列的存储过程
CREATE OR REPLACE PROCEDURE P_TEST IS
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 OR REPLACE VIEW RESULT AS '|| V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
----------------------------------------------------------------结果
----------------执行存储过程,生成视图
BEGIN
P_TEST;
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
----------------第二部分测试数据
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;
----------------报告存储过程,生成视图
BEGIN
P_TEST;
END;
----------------结果
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 VIEW RESULT;
DROP PROCEDURE P_TEST;
DROP TABLE TEST;
---------------------------------------------------------------------------------------------------------
DECLARE
v_sql VARCHAR2(1000);
BEGIN
/*create table AIDW.ST_RPT_CONTENT_201202
(
OP_TIME NUMBER not null,
REP_NO NUMBER not null,
CITY_ID NUMBER not null,
ZB_CODE VARCHAR2(32) not null,
ZB_VALUE VARCHAR2(128) default '0'
)*/
/* select 'create table tp_temp tablespace tbs_ai_temp as select city_id, '||wmsys.wm_concat('sum(case zb_code when '''|| zb_code ||''' then to_number(zb_value) else 0 end) "'||zb_code||'" ')
||' from aidw.st_rpt_content_201202 group by city_id' into v_sql from (
select distinct zb_code from aidw.st_rpt_content_201202 where rep_no =21101020060 and rownum<10
) ;
EXECUTE IMMEDIATE v_sql; */
select ' select city_id, '||wmsys.wm_concat('sum(case zb_code when '''|| zb_code ||''' then to_number(zb_value) else 0 end) "'||zb_code||'" ')
||' from aidw.st_rpt_content_201202 group by city_id' into v_sql from (
select distinct zb_code from aidw.st_rpt_content_201202 where rep_no =21101020060 and rownum<10
) ;
open rc for v_sql;
END;
评论0