[转]ORACLE 行转列问题

常瑛琭

/*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次
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;   

0
0
0