SQL Server2005 中用 Pivot 生成交叉表

最近在做一个数据统计，要求对一个表中的数据按照两个维度呈现，也就是传统的交叉表

比如，有一个问题表，有三个字段，（标题、问题类别、问题状态）

要求按照不同的类别，分别统计处各个状态的问题数量（如：产品问题中未处理的数量、服务问题中遗留问题数量等等）。

经过查找和尝试，终于生成了结果，现在分享给大家。

通过 Sql 2005 中的 Pivot 函数，可以方便的制作交叉表。

关于Pivot的用法，就不详细说了，大家可以百度一下，或者看sql server的帮助文档

 

下面是代码部分

 

 --问题分类表 CREATE TABLE [dbo].[QuestionClass]( [QuestionClassID] [int] IDENTITY(1,1) NOT NULL, [QuestionClassName] [nvarchar](50) NULL, CONSTRAINT [PK_QuestionClass] PRIMARY KEY CLUSTERED ( [QuestionClassID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --问题状态表 CREATE TABLE [dbo].[QuestionState]( [QuestionStateID] [int] IDENTITY(1,1) NOT NULL, [QuestionStateName] [nvarchar](50) NULL, CONSTRAINT [PK_QuestionState] PRIMARY KEY CLUSTERED ( [QuestionStateID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --问题记录表 CREATE TABLE [dbo].[Question]( [QuestionID] [int] IDENTITY(1,1) NOT NULL, [Topic] [nvarchar](50) NULL, [ClassID] [int] NULL, [StateID] [int] NULL, CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED ( [QuestionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] --初始化状态数据 insert into QuestionState(QuestionStateName) select '未处理' union all select '已处理' union all select '无效反馈' --初始化类别数据 insert into QuestionClass(QuestionClassName) select '产品问题' union all select '销售问题' union all select '服务问题' union all select '其他问题' --初始化问题数据 insert into Question(Topic,ClassID,StateID) select '产品问题1',1,1 union all select '产品问题2',1,2 union all select '产品问题3',1,3 union all select '产品问题4',1,2 union all select '销售问题1',2,2 union all select '销售问题2',2,2 union all select '销售问题3',2,1 union all select '销售问题4',2,2 union all select '服务问题1',3,1 union all select '服务问题2',3,2 union all select '服务问题3',3,3 union all select '服务问题4',3,1 union all select '其他问题1',4,1 --生成交叉表 select * from ( select QuestionID,QuestionStateName,QuestionClassName as 问题类别,qc.QuestionClassID from QuestionClass qc left join Question q on qc.QuestionClassID = q.ClassID left join QuestionState qs on q.StateID = qs.QuestionStateID ) as ff pivot( count(QuestionID) for QuestionStateName in([未处理],[已处理],[无效反馈]) ) as r order by QuestionClassID

 

 

结果截图：

 

进一步思考：

经过使用大家会发现，由于pivot中的字段必须是静态输入的，in([未处理],[已处理],[无效反馈])

这使得程序不够灵活，比如我增加了一个状态，那就需要修改SQL语句

能不能把这些字段也变成动态生成的呢，答案是肯定的。

我们可以通过自定义函数，读取QuestionClass生成 in 部分的字段。

 

动态生成sql：

--创建自定义函数 Create FUNCTION [dbo].[GetQuestionClassForClumn]() RETURNS varchar(max) AS BEGIN declare @ret varchar(max) set @ret='' select @ret=@ret+',['+cast(QuestionStateName as varchar)+']' from QuestionState order by QuestionStateID if @ret<>'' set @ret=substring(@ret,2,len(@ret)-1) RETURN @ret END --拼接SQL字符串 Declare @SQL varchar(max) set @SQL = ' select * from ( select QuestionID,QuestionStateName,QuestionClassName as 问题类别,qc.QuestionClassID from QuestionClass qc left join Question q on qc.QuestionClassID = q.ClassID left join QuestionState qs on q.StateID = qs.QuestionStateID ) as ff pivot( count(QuestionID) for QuestionStateName in('+dbo.GetQuestionClassForClumn()+') ) as r order by QuestionClassID ' --执行SQL exec(@SQL)

这样可以得到同样的结果。

 

总结：

通过Pivot和动态生成sql的方法，可以使交叉表统计更加灵活。