有时候,我们经常需要通过纵向存储的数据,在做报表的时候,横向输出显示出来。
最典型的例子:
问卷系统中的存储答案的表,存储的数据类似于(存储形式):
c_UserId (用户编号) c_QuestionId(问题编号) c_Item(答案)
--------------------------------------------------------------------------------------
1 1 A
1 2 B
1 3 C
1 4 D
2 1 D
2 2 D
2 3 A
2 4 B
以上表中存储了俩个用户的答题信息。
但是我们可能需要在做报表的时候,需要输出类似于这样的信息(输出形式):
c_UserId (用户编号) Q1(问题编号1) Q2(问题编号2) Q3(问题编号3) Q4(问题编号4)
------------------------------------------------------------------------------------------------------------
1 A B C D
2 D D A B
我们需要借组 sql server2005 中的pivot 函数+聚合函数来实现,但是,这个函数,个人感觉还是不是太灵活(呵呵 ,本人有点菜),所以,写了一个存储过程来对这个pivot 函数的转换列进行了封装。调用起来很方便,自动获取纵向表中相同行的最大值,不会当心遗漏某些行。
下面是源码(在这个存储过程中,使用了中间表,估计会有效率问题,希望大虾们不吝赐教):
/**
* 翻转表(纵向表横向输出)
*/
if exists(select * from sysobjects where name='Proc_Pivot_Table')
drop proc Proc_Pivot_Table
go
create proc Proc_Pivot_Table
@Table varchar(50) --表名
,@ColumName varchar(50) --要横向显示的字段名 答案
,@ColumKeyItem varchar(50) --要横向显示的字段名的Key字段名 问题编号
,@KeyColumItem varchar(50) --横向显示的Key字段名 用户编号
,@ColumAsName varchar(50)=null --key指定别名(要横向显示的字段列名称)
as
begin
--设置要横向显示的字段列名称,如果没有设置,则默认取“要横向显示的字段名”作为列名称
if (@ColumAsName is null) set @ColumAsName=@ColumName
declare @MaxColums int
exec('if exists( select * from sysobjects where name=''temp_T1'')
drop table temp_T1 ')
exec (' select top 1 count(Convert(varchar(max),'+@ColumName+')) as c_ItemsNum
INTO temp_T1 from '+@Table+' group by '+@KeyColumItem+' order by c_ItemsNum desc')
select @MaxColums=c_ItemsNum from temp_T1
print @MaxColums
declare @i int,@SQL varchar(8000),@filds varchar(8000)
set @i=1
--拼接sql
set @SQL='SELECT '+@KeyColumItem+' '
--开始拼接查询字段
set @filds=''
while(@i<=@MaxColums)
begin
set @filds=@filds+' , ['+Convert(varchar(5),@i)+']'
set @SQL=@SQL+' , max(['+Convert(varchar(5),@i)+']) as '+@ColumAsName+Convert(varchar(5),@i)+' '
set @i=@i+1
end
--开始转置
set @SQL=@SQL+' from '+@Table+' pivot (max('+@ColumName+')for '+@ColumKeyItem+' in
( [0] '+@filds+')) as pvt Group by '+@KeyColumItem
print @SQL
exec(@SQL)
end
go
下面是测试用例
--------------------------------------------------------------------------------------------------------------------------
/**
* 构造用利
*/
if exists(select * from sysobjects where name='T_Answer_Test')
drop table T_Answer_Test
go
create table T_Answer_Test(
c_Id int identity(1,1) not null, --序号
c_UserId int, --用户编号
c_QuestionId int, --问题编号
c_Item nvarchar(max) --答案
)
go
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('1','1','A')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('1','2','A')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('1','3','B')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('1','4','C')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('1','5','D')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('1','6','A')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('2','1','D')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('2','2','')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('3','1','A')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('3','2','B')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('3','3','')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('3','4','D')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('3','5','D')
insert into T_Answer_Test(c_UserId,c_QuestionId,c_Item) values('3','6','')
----------------------------------------------------
/**
* 测试
*/
DECLARE
@Table varchar(50) --表名
,@ColumName varchar(50) --要横向显示的字段名 答案
,@ColumKeyItem varchar(50) --要横向显示的字段名的Key字段名 问题编号
,@KeyColumItem varchar(50) --横向显示的Key字段名 用户编号
,@ColumAsName varchar(50) --key指定别名
set @Table='T_Answer_Test'--答案表名称
set @ColumName='c_Item'--选项
set @KeyColumItem='c_UserId'--用户编号
set @ColumKeyItem='c_QuestionId'--问题编号
set @ColumAsName='Q'--问题别名
--显示原始数据
select c_UserId,c_QuestionId,c_Item from T_Answer_Test
--横向输出数据
exec Proc_Pivot_Table @Table,@ColumName,@ColumKeyItem,@KeyColumItem,@ColumAsName