select LOCAL, sum(decode(PRODUCTS,'A',number,0)) A, sum(decode(PRODUCTS,'B',number,0)) B, sum(decode(PRODUCTS,'C',number,0)) C, .... sum(decode(PRODUCTS,'D',number,0)) Z from TableName group by Local;
.. tempI number(10); tempS Varchar2(1024); FieldVal TableName.PRODUCTS%type; begin select count(distinct PRODUCTS) into tempI from TableName; tempS:='Create table NewTable As select Local'; for c in (select count(distinct PRODUCTS) PRD from TableName) loop FieldVal:=c.PRD S:=S+',sum(decode(Products,'''+FieldVal+',NUMBER,0)'; end loop; S:=S+' from TableName group by Local'; EXECUTE IMMEDIATE V_SQL;
end;写的比较乱,基本上就是这个样子
删除: tempI number(10); select count(distinct PRODUCTS) into tempI from TableName; EXECUTE IMMEDIATE V_SQL; --》 EXECUTE IMMEDIATE tempS;
select local,sum(case when produts='A' then number else 0 end ) from table group by local union select local,sum(case when produts='B' then number else 0 end ) from table group by local union select local,sum(case when produts='C' then number else 0 end ) from table group by local
这个在SQLSERVER里面很好实现,ORACLE好象没哪个功能,来学习吧
这种列数不确定的交叉表,在ms sqlserver中很好实现: declare @sql varchar(8000) set @sql = 'select name,' select @sql = @sql + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''',' from (select distinct subject from test) as a select @sql = left(@sql,len(@sql)-1) + ' from test group by name' exec(@sql) go 但在oracle中还没试过
Oracle中也有类似exec的函数,什么名字忘了,有谁知道说一声
回头再看,之前写的有些笔误,纠正如下: Create Or Replace Procedure selltable2reporttable As temps Varchar2(1024); fieldval tablename.products%Type; Begin temps := 'Create table REPORTTABLE As select Local '; For c In (Select Distinct products prd From tablename) Loop fieldval := c.prd temps := temps + ',sum(decode(Products,''' + fieldval + ''',NUMBER,0)'; End Loop; temps := temps + ' from SELLTABLE group by Local'; Execute Immediate temps; End; /
TO codoxo() :怎么有编译错误呢?请帮帮忙看看
declare cursor qa is select distinct PRODUCTS from SELLTABLE; cursor qb is select distinct local from SELLTABLE; ls varchar2(4000); LS_LOCAL varchar2(4000); N NUMBER; begin ls := 'local'; for tqa in qa loop ls := ls ||chr(9)||tqa.PRODUCTS ; --TAB键的ASC码,具体可查一下 END LOOP; DBMS_OUT.PUTLINE(LS); FOR TQB IN QB LOOP LS:= TQB.LOCAL; LS_LOCAL = LS; for tqC in qa loop SELECT SUM(NUMBER) INTO N FROM SELLTABLE WHERE PRIDUCTS=TQC.PRODUCTS AND LOCAL= LS_LOCAL; LS :=LS ||chr(9)|| N; END LOOP; DBMS_OUT.PUTLINE(LS); END LOOP;
END ; / 若PRODUCTS 比较多导致LS长度超过255,可稍作改动先写表 execute immediate ("crteate table "); 或用create global temparory table;
呵呵 用decode 或者是case when then ... end case
Create Or Replace Procedure selltable2reporttable As temps Varchar2(1024); fieldval tablename.products%Type; Begin temps := 'Create table REPORTTABLE As select Local '; For c In (Select Distinct products prd From tablename) Loop fieldval := c.prd; temps := temps + ',sum(decode(Products,''' + fieldval + ''',NUMBER,0)'; End Loop; temps := temps + ' from SELLTABLE group by Local'; Execute Immediate temps; End; /
LOCAL,
sum(decode(PRODUCTS,'A',number,0)) A,
sum(decode(PRODUCTS,'B',number,0)) B,
sum(decode(PRODUCTS,'C',number,0)) C,
....
sum(decode(PRODUCTS,'D',number,0)) Z
from TableName
group by Local;
tempI number(10);
tempS Varchar2(1024);
FieldVal TableName.PRODUCTS%type;
begin
select count(distinct PRODUCTS) into tempI from TableName;
tempS:='Create table NewTable As select Local';
for c in (select count(distinct PRODUCTS) PRD from TableName) loop
FieldVal:=c.PRD
S:=S+',sum(decode(Products,'''+FieldVal+',NUMBER,0)';
end loop;
S:=S+' from TableName group by Local';
EXECUTE IMMEDIATE V_SQL;
end;写的比较乱,基本上就是这个样子
tempI number(10);
select count(distinct PRODUCTS) into tempI from TableName;
EXECUTE IMMEDIATE V_SQL;
--》
EXECUTE IMMEDIATE tempS;
---》
S:=S+',sum(decode(Products,'''+FieldVal+''',NUMBER,0)';
发完了才发现有笔误。
union
select local,sum(case when produts='B' then number else 0 end ) from table group by local
union
select local,sum(case when produts='C' then number else 0 end ) from table group by local
declare @sql varchar(8000)
set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go
但在oracle中还没试过
Create Or Replace Procedure selltable2reporttable As
temps Varchar2(1024);
fieldval tablename.products%Type;
Begin
temps := 'Create table REPORTTABLE As select Local ';
For c In (Select Distinct products prd From tablename) Loop
fieldval := c.prd temps := temps + ',sum(decode(Products,''' +
fieldval + ''',NUMBER,0)';
End Loop;
temps := temps + ' from SELLTABLE group by Local';
Execute Immediate temps;
End;
/
cursor qa is
select distinct PRODUCTS from SELLTABLE;
cursor qb is
select distinct local from SELLTABLE;
ls varchar2(4000);
LS_LOCAL varchar2(4000);
N NUMBER;
begin
ls := 'local';
for tqa in qa loop
ls := ls ||chr(9)||tqa.PRODUCTS ; --TAB键的ASC码,具体可查一下
END LOOP;
DBMS_OUT.PUTLINE(LS);
FOR TQB IN QB LOOP
LS:= TQB.LOCAL;
LS_LOCAL = LS;
for tqC in qa loop
SELECT SUM(NUMBER) INTO N FROM SELLTABLE WHERE PRIDUCTS=TQC.PRODUCTS AND LOCAL= LS_LOCAL;
LS :=LS ||chr(9)|| N;
END LOOP;
DBMS_OUT.PUTLINE(LS);
END LOOP;
END ;
/
若PRODUCTS 比较多导致LS长度超过255,可稍作改动先写表
execute immediate ("crteate table ");
或用create global temparory table;
temps Varchar2(1024);
fieldval tablename.products%Type;
Begin
temps := 'Create table REPORTTABLE As select Local ';
For c In (Select Distinct products prd From tablename) Loop
fieldval := c.prd;
temps := temps + ',sum(decode(Products,''' +
fieldval + ''',NUMBER,0)';
End Loop;
temps := temps + ' from SELLTABLE group by Local';
Execute Immediate temps;
End;
/