我所了解的创建动态交叉表有两种方法,如下图,有这样一张销售表 ,我想统计个人的销售业绩,如图二表示出来,
创建销售表
CREATE TABLE [dbo].[销售](
[ID] [int] NOT NULL,
[员工姓名] [nvarchar](25) NULL,
[所在部门] [nvarchar](15) NULL,
[销售业绩] [int] NULL
) ON [PRIMARY]
图一,图二
方法一:使用游标创建动态交叉表
CREATE procedure Corss
@strTabName as varchar(50) = '销售',
@strCol as varchar(50) = '所在部门',
@strGroup as varchar(50) = '员工姓名',--分组字段
@strNumber as varchar(50) = '销售业绩', --被统计的字段
@strSum as varchar(10) = 'Sum' --运算方式
AS
DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标
begin
SET nocount ON
SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strNumber + ']' --查询的前半段
OPEN corss_cursor
while (0=0)
BEGIN
FETCH NEXT FROM corss_cursor --遍历游标,将列头信息放入变量@strTmpCol
INTO @strTmpCol
if (@@fetch_status<>0) break
SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ']' --构造查询
END
SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup --查询结尾
EXECUTE(@strsql) --执行
IF @@error <>0 RETURN @@error --如果出错,返回错误代码
CLOSE corss_cursor
DEALLOCATE corss_cursor RETURN 0 --释放游标,返回0表示成功
end
输出动态交叉表
DECLARE @RC int
DECLARE @strTabName varchar(50)
DECLARE @strCol varchar(50)
DECLARE @strGroup varchar(50)
DECLARE @strNumber varchar(50)
DECLARE @strSum varchar(10)
EXEC @RC = [db_Chapter5].[dbo].[Corss] DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
DECLARE @PrnLine nvarchar(4000)
PRINT '存储过程: db_Chapter5.dbo.Corss'
SELECT @PrnLine = ' 返回代码 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
二:使用递归的select变量,创建动态交叉表
DECLARE @strSql as varchar(1000)
select @strSql=isnull(@strSql, '')+char(13)+' ,sum(case when [所在部门] = '''+[所在部门]+''' then [销售业绩] else null end) as ['+[所在部门]+']'
from (select distinct [所在部门] from 销售) a
SET @strsql ='select [员工姓名],sum([销售业绩]) as [ 销售业绩] ' + @strSql + ' from 销售 group by [员工姓名]'
exec(@strsql)
个人更喜欢使用方法二,执行速度相比较游标很快,而且所需的代码量也少。