CREATE PROCEDURE OpenCrossTable
 @rows VARCHAR(1000),  --汇总字段,可以是一个或者多个字段,如:'spbh,spmch,shpgg,shpchd'
 @cols VARCHAR(1000),  --列表题字段,只能是单个字段,如'bm',最终结果形如: 采购部 销售部 仓储部
 @sumfld VARCHAR(1000), --求合字段,只能是单个字段,如'hsje'.
 @aggFun VARCHAR(1000), --聚合函数,指明汇总运算方式
 @isSum CHAR(1) --是否显示合计项
AS
--by lixd 2005-09-19
DECLARE @sql VARCHAR(8000),@tmpsql VARCHAR(8000)

SET @tmpsql='select distinct '+@cols+' AS c1 into ##T1 from #t_query'

EXEC(@tmpsql)

SET @sql='select '+@rows+','

SELECT @sql=@sql+''''+RTRIM(c1)+'''='+@aggFun+'(case when '+@cols+'='''+RTRIM(c1)+''' then '+@sumfld+' else 0 end),' FROM ##T1

IF @isSum='Y'
 SET @sql=@sql+'合计=sum(hsje) '
ELSE
 SET @sql=left(@sql,len(@sql)-1)

SET @sql=@sql+' from #t_query group by '+@rows

EXEC(@sql)

DROP TABLE ##T1

GO

--测试数据
create table #t_query(
 rq char(10) null default '',
 bm char(20) null default '',
 hsje decimal(14,2) null default 0
)

insert into #t_query(rq,bm,hsje) values('2005-09-01','bm1',10)
insert into #t_query(rq,bm,hsje) values('2005-09-01','bm2',15)
insert into #t_query(rq,bm,hsje) values('2005-09-02','bm2',11)
insert into #t_query(rq,bm,hsje) values('2005-09-05','bm3',12)

exec OpenCrossTable 'rq','bm','hsje','sum','Y'

/*
bm  rq   hsje
bm1  2005-09-01 10
bm2  2005-09-01 15
bm2  2005-09-02 11
bm3  2005-09-05 12

rq   bm1  bm2  bm3
2005-09-01 10  15  0
2005-09-02 0  11  0
2005-09-05 0  0  12
*/