一个典型面试题--写一存储过程，请高手帮忙！！谢了

[ojahe]

有如下销售记录表SELLTABLE：

LOCAL PRODUCTS NUMBER
北京 A 10
北京 B 12
北京 D 123
上海 A 257
上海 C 142
天津 D 214
北京 A 20
北京 B 20
北京 D 123
上海 A 76969
上海 C 2536
.... .... .....
请写一个存储过程,将其变成如下的统计表REPORTTABLE:

LOCAL A B C D .........
北京 30 32 0 246 ...
上海 .. .. .. .. ..
天津 ... .. .. .. ..
..... .. .. .. .. ..

[chendzcs]

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;

[bzszp]

用存储过程。

[Eddie005]

......

[chendzcs]

..
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;

写的比较乱，基本上就是这个样子

[chendzcs]

删除：
tempI number(10);
select count(distinct PRODUCTS) into tempI from TableName;
EXECUTE IMMEDIATE V_SQL;
－－》
EXECUTE IMMEDIATE tempS;

[chendzcs]

S:=S+',sum(decode(Products,'''+FieldVal+',NUMBER,0)';
－－－》
S:=S+',sum(decode(Products,'''+FieldVal+''',NUMBER,0)';
发完了才发现有笔误。

[dinya2003]

:)
哪个公司还用这种方式来招聘人?

[andzen]

在存储过程里面用动态SQL 可以实现任意记录的情况,不过报表基本上没有什么技术含量,只是体力和小技巧我以前就做过这样的,其实比这个更复杂要求每行只能显示4个列,最后要汇总;有需要的话回公司把代码贴上来;

[幽奇]

学习!这个招聘方法也够有水准哦!

[skyyiyang]

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

[zonelive]

这个在SQLSERVER里面很好实现，ORACLE好象没哪个功能，来学习吧

[haifeng1012]

支持codoxo的写法！！

[xdd1125]

这种列数不确定的交叉表，在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中还没试过

[jackyhe_oracle]

Oracle中也有类似exec的函数，什么名字忘了，有谁知道说一声

[chendzcs]

回头再看，之前写的有些笔误，纠正如下：
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;
/

[ojahe]

TO codoxo() ：

怎么有编译错误呢？请帮帮忙看看

[xdd1125]

up

[nbnasom]

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;

[maohuijian]

呵呵 用decode 或者是case when then ... end case

[chendzcs]

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;
/