交叉表—用存储过程实现的普通交叉表(一)

开发工具：C#2008/2010

数据库：Sql Server 2000/2005/2008

界面：WinForm

   关于交叉表，各位网友用各种方法实现的不少了，本文主要从用存储过程实现交叉表功能进行探讨，这种方法给具体用户也做了不少。界面如下：

一、基本数据结构

（一）销售单主表结构

（二）销售单明细明

(三）由上述两表组成的视图

二、存储过程

--业务经理销售或兑换分品种按客户汇总表
CREATE PROCEDURE YWYXSDHFPZKHHZ
(
@TJTS int,
@StartDate VARCHAR(20),
@EndDate VARCHAR(20),
@YWYID VARCHAR(50),
@KHXZ VARCHAR(50),
@TJFW VARCHAR(50),
@ProCate VARCHAR(50),
@ClientCate VARCHAR(50),
@ChanDi VARCHAR(50),
@returnstring nvarchar(200) out
)

AS

set @StartDate =@StartDate+ ‘ 00:00:00‘--查询的开始日期
set @EndDate =@EndDate+ ‘ 23:59:59‘--查询的结束日期
set @returnstring=‘日均销量:000‘

DECLARE @FIELD VARCHAR(50),@SQL nvarchar(2000),@SQLCondition varchar(100),@KHMC varchar(50),@chandiA varchar(50),@sl float,@FinalSL varchar(50)

set @SQLCondition=‘ and 1=1‘
--业务经理条件
if(@YWYID<>‘‘)
begin
set @SQLCondition=@SQLCondition+‘ and ywybm=‘‘‘+@YWYID+‘‘‘‘
end
--客户性质
if(@KHXZ<>‘‘)
begin
set @SQLCondition=@SQLCondition+‘ and kh_flfs=‘‘‘+@KHXZ+‘‘‘‘
end
--产品分类
if(@ProCate<>‘‘)
begin
set @SQLCondition=@SQLCondition+‘ and CateID=‘‘‘+@ProCate+‘‘‘‘
end
--客户分类
if(@ClientCate<>‘‘)
begin
set @SQLCondition=@SQLCondition+‘ and ClientCateID=‘‘‘+@ClientCate+‘‘‘‘
end
--品种
if(@ChanDi<>‘‘)
begin
set @SQLCondition=@SQLCondition+‘ and Chandi= ‘‘‘+@ChanDi+‘‘‘‘
end

--创建临时过渡表结构
Create table #tab1
(
KHMC varchar(50),
pmgg varchar(50),
chandi varchar(50),
sl float
)
--生成临时汇总表结构
Create table #tab2
(
KHMC varchar(50),
chandi varchar(50),
sl float
)
if(@TJFW=‘‘)--销售和兑换数据全部统计
begin
--销售数据
set @SQL=‘SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProCKDPro WHERE pzrq>=‘‘‘+@StartDate+‘‘‘ AND pzrq<=‘‘‘+@EndDate+‘‘‘ AND lbmc=‘‘销售‘‘ and redword=‘‘F‘‘ ‘+@SQLCondition+‘ GROUP BY KHMC,pmgg,chandi ORDER BY KHMC‘
insert into #tab1 EXEC sp_ExecuteSql @SQL
--兑换数据
set @SQL=‘SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProDHDPro WHERE pzrq>=‘‘‘+@StartDate+‘‘‘ AND pzrq<=‘‘‘+@EndDate+‘‘‘ and redword=‘‘F‘‘ ‘+@SQLCondition+‘ GROUP BY KHMC,pmgg,chandi ORDER BY KHMC‘
insert into #tab1 EXEC sp_ExecuteSql @SQL
end
else
begin
if(@TJFW=‘销售‘)--仅统计销售数据
begin
--销售数据
set @SQL=‘SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProCKDPro WHERE pzrq>=‘‘‘+@StartDate+‘‘‘ AND pzrq<=‘‘‘+@EndDate+‘‘‘ AND lbmc=‘‘销售‘‘ and redword=‘‘F‘‘ ‘+@SQLCondition+‘ GROUP BY KHMC,pmgg,chandi ORDER BY KHMC‘
insert into #tab1 EXEC sp_ExecuteSql @SQL
end
else--仅统计兑换
begin
set @SQL=‘SELECT KHMC,pmgg,chandi,sum(sl) as sl FROM VIProDHDPro WHERE pzrq>=‘‘‘+@StartDate+‘‘‘ AND pzrq<=‘‘‘+@EndDate+‘‘‘ and redword=‘‘F‘‘ ‘+@SQLCondition+‘ GROUP BY KHMC,pmgg,chandi ORDER BY KHMC‘
insert into #tab1 EXEC sp_ExecuteSql @SQL
end
end
--把品种为空的用品名替换
update #tab1 set chandi=pmgg where chandi=‘‘

--再次按品种汇总，并插入临时汇总表
insert into #tab2 select KHMC,chandi,sum(sl) as sl from #tab1 group by KHMC,chandi order by KHMC

--创建临时表，生成给用户的交叉表结构
Create table #tab3
(
客户名称 varchar(50),
合计数量 float
)

--在添加字段前先用#tab2计算结果表给#tab3临时表追加数据,而且必须加distinct
insert into #tab3 (客户名称,合计数量) select KHMC,sum(sl) as sl from #tab2 group by khmc order by KHMC

--给#tab3增加字段

DECLARE CUR_FIELD CURSOR LOCAL FOR SELECT distinct chandi FROM #tab2 order by chandi
OPEN CUR_FIELD
FETCH CUR_FIELD INTO @FIELD
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @FIELD=‘[‘+@FIELD+‘]‘
set @SQL=‘alter table #tab3 Add ‘+ @FIELD +‘ FLOAT not NULL DEFAULT 0 With Values‘
EXEC(@SQL)
FETCH CUR_FIELD INTO @FIELD
END

--关闭游标
CLOSE CUR_FIELD
deallocate CUR_FIELD

--给#tab3表的新增字段更新数据

declare CUR_SUM cursor for select KHMC,chandi,sl from #tab2
OPEN CUR_SUM
FETCH CUR_SUM INTO @KHMC,@chandiA,@sl
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @FIELD=‘[‘+@chandiA+‘]‘,@FinalSL=CAST(@sl AS VARCHAR(50))
SELECT @SQL=‘UPDATE #tab3 SET ‘+@FIELD+‘=‘+@FinalSL+‘ WHERE [客户名称]=‘‘‘+@KHMC+‘‘‘‘
EXEC(@SQL)
FETCH CUR_SUM INTO @KHMC,@chandiA,@sl
END

--关闭游标
CLOSE CUR_SUM
deallocate CUR_SUM

declare @TotalCount float --总销量
declare @averageCount float --日均销量
declare @MFCount float --面粉销量
declare @MTCount float --面条销量
--计算日均销量
select @TotalCount=(select round(sum(sl),2) as sl from #tab2)
if(@TJTS=0)
begin
set @averageCount=@TotalCount
END
else
begin
set @averageCount=round(@TotalCount/@TJTS,2)
END

--计算面粉销量
select @MFCount=(select round(sum(sl),2) as sl from #tab1 where pmgg like ‘%粉%‘ and pmgg not like ‘%条%‘)
--计算面条销量
select @MTCount=(select round(sum(sl),2) as sl from #tab1 where pmgg like ‘%面条%‘)

--日均销量、面粉合计、面条合计返回变量
set @returnstring=‘日均销量：‘+convert(varchar(100),convert(decimal(18,2),@averageCount))+‘ 面粉合计数量：‘+convert(varchar(100),convert(decimal(18,2),@MFCount))+‘ 面条合计数量：‘+convert(varchar(100),convert(decimal(18,2),@MTCount))
select * from #tab3

--删除临时表#tab1
drop table #tab1
drop table #tab2
drop table #tab3
GO

交叉表—用存储过程实现的普通交叉表(一)