with t as (select 'zhangsan' nm, '2014-01-01' dt, 100 amt from dual union all select 'lisi' nm, '2014-03-01' dt, 100 amt from dual union all select 'lisi' nm, '2014-03-01' dt, 100 amt from dual union all select 'wangwu' nm, '2014-05-01' dt, 100 amt from dual) select distinct nm, (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '01') "01", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '02') "02", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '03') "03", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '04') "04", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '05') "05", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '06') "06", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '07') "07", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '08') "08", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '09') "09", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '10') "10", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '11') "11", (select sum(amt) from t t2 where t2.nm = t1.nm and substr(t2.dt, 6, 2) = '12') "12" from t t1 union all select distinct 'total', (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '01') "01", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '02') "02", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '03') "03", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '04') "04", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '05') "05", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '06') "06", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '07') "07", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '08') "08", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '09') "09", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '10') "10", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '11') "11", (select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '12') "12" from t t1;
我改了以后运行有问题: create table biao1(nname VARCHAR(128),bb date,moneyy NUMeric(20,2)); insert into biao1 VALUES('张三',TO_DATE('20050101','YYYYMMDD'),111.11); insert into biao1 VALUES('张三',TO_DATE('20051201','YYYYMMDD'),111.11); insert into biao1 VALUES('李四',TO_DATE('20060101','YYYYMMDD'),111.11); Declare I int; Declare S varchar2(4000); Select S='',I=1; While I<=12; Begin Select S=S+', SUM(Case Month([bb]) When '+Rtrim(I)+' Then moneyy Else 0 End) As ['+Right(100+I,2)+N'月]'; Select I=I+1; End; Select S=N'Select IsNull(nname,N''总计'') As name '+S+N',SUM([moneyy]) As 总计 From biao1 Group By nname With Rollup'; --Select @S EXEC(S); 依葫芦画瓢出不了结果,所以上来求教了
create table biao1(nname VARCHAR(128),bb DATE,mmoney NUMBER(20,2)) insert into biao1 select '张三',to_date('2005-01-01','yyyy-mm-dd'),111.11 FROM dual union all select '张三',to_date('2005-01-02','yyyy-mm-dd'),111.11 FROM dual union all select '李四',to_date('2005-12-02','yyyy-mm-dd'),111.11 FROM dual GO Declare I NUMBER S VARCHAR2(4000) BEGIN I := 1; While(I <= 12) LOOP Select @S=@S+', SUM(Case Month([bb]) When '+Rtrim(@I)+' Then mmoney Else 0 End) As ['+Right(100+@I,2)+N'月]'; Select @I=@I+1 END LOOP; S='select NVL(nname,'总计') As name '+@S+N',SUM([mmoney]) As 总计 From biao1 Group By nname With Rollup'; --Select @S EXECUTE IMMEDIATE S; END;loop里面的函数你自己改成oracle的吧
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); CREATE OR REPLACE PROCEDURE P_TEST AUTHID CURRENT_USER 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; commit; END; ----------------执行存储过程,生成视图 BEGIN P_TEST; END; ----------------结果 SELECT * FROM RESULT T;
with t as
(select 'zhangsan' nm, '2014-01-01' dt, 100 amt
from dual
union all
select 'lisi' nm, '2014-03-01' dt, 100 amt
from dual
union all
select 'lisi' nm, '2014-03-01' dt, 100 amt
from dual
union all
select 'wangwu' nm, '2014-05-01' dt, 100 amt
from dual)
select distinct nm,
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '01') "01",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '02') "02",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '03') "03",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '04') "04",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '05') "05",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '06') "06",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '07') "07",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '08') "08",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '09') "09",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '10') "10",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '11') "11",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '12') "12"
from t t1
union all
select distinct 'total',
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '01') "01",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '02') "02",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '03') "03",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '04') "04",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '05') "05",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '06') "06",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '07') "07",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '08') "08",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '09') "09",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '10') "10",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '11') "11",
(select sum(amt) from t t2 where substr(t2.dt, 6, 2) = '12') "12"
from t t1;
create table biao1(nname VARCHAR(128),bb date,moneyy NUMeric(20,2));
insert into biao1 VALUES('张三',TO_DATE('20050101','YYYYMMDD'),111.11);
insert into biao1 VALUES('张三',TO_DATE('20051201','YYYYMMDD'),111.11);
insert into biao1 VALUES('李四',TO_DATE('20060101','YYYYMMDD'),111.11);
Declare I int;
Declare S varchar2(4000);
Select S='',I=1;
While I<=12;
Begin
Select S=S+', SUM(Case Month([bb]) When '+Rtrim(I)+' Then moneyy Else 0 End) As ['+Right(100+I,2)+N'月]';
Select I=I+1;
End;
Select S=N'Select IsNull(nname,N''总计'') As name '+S+N',SUM([moneyy]) As 总计 From biao1 Group By nname With Rollup';
--Select @S
EXEC(S);
依葫芦画瓢出不了结果,所以上来求教了
insert into biao1 select '张三',to_date('2005-01-01','yyyy-mm-dd'),111.11 FROM dual
union all select '张三',to_date('2005-01-02','yyyy-mm-dd'),111.11 FROM dual
union all select '李四',to_date('2005-12-02','yyyy-mm-dd'),111.11 FROM dual
GO
Declare
I NUMBER
S VARCHAR2(4000)
BEGIN
I := 1;
While(I <= 12)
LOOP
Select @S=@S+', SUM(Case Month([bb]) When '+Rtrim(@I)+' Then mmoney Else 0 End) As ['+Right(100+@I,2)+N'月]';
Select @I=@I+1
END LOOP;
S='select NVL(nname,'总计') As name '+@S+N',SUM([mmoney]) As 总计 From biao1 Group By nname With Rollup';
--Select @S
EXECUTE IMMEDIATE S;
END;loop里面的函数你自己改成oracle的吧
oracle的变量声明是
sql varchar2(80);
赋值是sql :='111';
引用直接用变量名
你改改就是了
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);
CREATE OR REPLACE PROCEDURE P_TEST AUTHID CURRENT_USER 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;
commit;
END;
----------------执行存储过程,生成视图
BEGIN
P_TEST;
END;
----------------结果
SELECT * FROM RESULT T;
参考这个,应该可以自己解决了。