一个动态交叉报表的例子

原文http://community.csdn.net/Expert/topic/5371/5371292.xml?temp=.5765955

表1（制造商号码作为主键)
制造商号码  制造商名
A1          Aname1
A2          Aname2
A3          Aname3
A4          Aname4
B1          Bname1
B2          Bname2
B3          Bname3

表2(制造商号码和商品号码联合作为主键)
制造商号码  商品号码   产量
A1          S1         1
A1          S2         2
A2          S2         3
A2          S3         4
A3          S1         5
A3          S4         6
A4          S3         7
A4          S5         8
B1          S6         9
B2          S6         10
B3          S1         11

要求做两个存储过程
1。存储过程CreateTempTable,一个Varchar型参数，例如：
   CreateTempTable 'A'
   则生成下表
   商品号码  制造商A1 制造商A2 制造商A3  制造商A4
   S1        1         0        5         0 
   S2        2         3        0         0  
   S3        0         4        0         7
   S4        0         0        6         0
   S5        0         0        0         8
   CreateTempTable 'B'
   则生成下表
   商品号码  制造商B1  制造商B2  制造商B3
   S1        0         0         11
   S6        9         10        0
   就是生成以输入参数开头的商品产量表，典型的行列转置问题，每次生成的表的列数和列名都不固定。
2。在1生成的表中任意改产量，用一个WriteSourceTable的存储过程，更新回表2

要求用尽量少的语句实现，语句（注意：不是指代码字符数）越少，分数越高。
(以SQL语句数量决定，比如Create Table可能要写很多行，但还是看作一条SQL语句。)

-----------------------------------------------------------------------

--创建测试环境
create table 表1(制造商号码 varchar(20) primary key,制造商名 varchar(20))
create table 表2(制造商号码 varchar(20),商品号码 varchar(20),产量 int)

--插入测试数据
insert 表1(制造商号码,制造商名)
select 'A1','Aname1' union all
select 'A22','Aname2' union all
select 'A333','Aname3' union all
select 'A4444','Aname4' union all
select 'B1','Bname1' union all
select 'B2','Bname2' union all
select 'B3','Bname3'

insert 表2(制造商号码,商品号码,产量)
select 'A1','S1','1' union all
select 'A1','S2','2' union all
select 'A22','S2','3' union all
select 'A22','S3','4' union all
select 'A333','S1','5' union all
select 'A333','S4','6' union all
select 'A4444','S3','7' union all
select 'A4444','S5','8' union all
select 'B1','S6','9' union all
select 'B2','S6','10' union all
select 'B3','S1','11'
go
--第一题,CreateTempTable动态创建表
create proc CreateTempTable @str varchar(8000),@tablename sysname
as
begin
 set nocount on
 set xact_abort on

 declare @col varchar(8000),@sql varchar(8000)
 select @col = 'create table '+ @tablename+ '(商品号码 varchar(20)' ,@sql = 'select 商品号码'

 select @col = @col + ',制造商' + 制造商号码 + ' varchar(20)'
  ,@sql = @sql +  ',sum(case 制造商号码 when ''' +  制造商号码 
   + ''' then 产量 else 0 end) as 制造商' + 制造商号码
 from 表1
 where 制造商号码 in (select 制造商号码 from 表2 where 制造商号码 like '%'+ @str+'%')
 order by 制造商号码

 select @col = @col + ')',@sql = @sql + ' from 表2 where 制造商号码 like ''%''
   +''' + @str + '''+ ''%''  group by 商品号码'
  ,@sql = replace(@sql,'''','''''')

 --事务保护
 begin tran
  if exists (select 1 from sysobjects where id = object_id(@tablename) and type = 'U')
   exec('drop table '+ @tablename)
  exec (@col) 
  exec ('insert '+ @tablename +  ' exec ('''+@sql+''')')
 commit tran
end
 
go
--测试
exec CreateTempTable 'A1','tablename'

select * from tablename
/*--测试结果
商品号码   制造商A1  
-------------------- -------------------- 
S1     1
S2     2
*/

--第二题
go
create proc WriteSourceTable @tablename sysname
as
begin
 set nocount on
 set xact_abort on
 
 if not exists (select 1 from sysobjects where id = object_id(@tablename) and type = 'U')
 begin     
  select '没有这个表！'
  return
 end

 declare @sql varchar(8000) set @sql = ''

 --事务保护,防止其它事务更改表@tablename的架构
 begin tran
  exec ('dbcc lockobjectschema (' + @tablename + ')')

  select @sql = @sql + ' union all select ''' + right(name,len(name)-3) 
    + ''' as 制造商,商品号码,' + name + ' as 产量 from '+ @tablename
  from(
   select name from syscolumns where id = object_id(@tablename) and name like '制造商%'  
  ) _x
  
  select @sql = stuff(@sql,1,11,'')
  
  create table #t(制造商号码 varchar(20),商品号码 varchar(20),产量 int)
  insert #t exec(@sql)
 commit tran

 begin tran
  update _a
  set _a.产量 = _b.产量
  from 表2 _a
  join #t _b on _b.制造商号码 = _a.制造商号码 and _b.商品号码 = _a.商品号码
 
  insert 表2
  select * from #t _t
  where not exists(
   select 1 from 表2 t 
   where t.制造商号码 = _t.制造商号码 and t.商品号码 = _t.商品号码
   )
   and _t.产量 > 0
 commit tran

 drop table #t
end
go

--修改数据
update tablename set 制造商A1 = 100

select * from tablename
/*
商品号码   制造商A1  
-------------------- -------------------- 
S1     100
S2     100
*/

--执行存储过程
exec WriteSourceTable 'tablename'

--查看结果
select * from 表2

--结果
/*
制造商号码  商品号码   产量   
-------------------- -------------------- ----------- 
A1     S1     100
A1     S2     100
A22    S2     3
A22    S3     4
A333   S1     5
A333   S4     6
A4444  S3     7
A4444  S5     8
B1     S6     9
B2     S6     10
B3     S1     11
*/

go
--删除测试环境
drop table 表2
drop table 表1
drop proc CreateTempTable,WriteSourceTable