日期:2014-05-17 浏览次数:20735 次
----------------建表
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';