sql server 纵向表横向输出的实现

有时候，我们经常需要通过纵向存储的数据，在做报表的时候，横向输出显示出来。

最典型的例子：

问卷系统中的存储答案的表，存储的数据类似于（存储形式）：

 

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

 

 

---

这里晒出来sql源码：

纵向表横向输出数据.sql