sql server 纵向表横向输出的实现
来源:互联网 发布:验光软件 编辑:程序博客网 时间:2024/05/06 17:35
有时候,我们经常需要通过纵向存储的数据,在做报表的时候,横向输出显示出来。
最典型的例子:
问卷系统中的存储答案的表,存储的数据类似于(存储形式):
c_UserId(用户编号)c_QuestionId(问题编号)c_Item(答案)
--------------------------------------------------------------------------------------
11A
12B
1 3C
1 4 D
21D
22D
2 3A
2 4B
以上表中存储了俩个用户的答题信息。
但是我们可能需要在做报表的时候,需要输出类似于这样的信息(输出形式):
c_UserId(用户编号) Q1(问题编号1)Q2(问题编号2)Q3(问题编号3)Q4(问题编号4)
------------------------------------------------------------------------------------------------------------
1ABCD
2DDAB
我们需要借组 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
@Tablevarchar(50)--表名
,@ColumNamevarchar(50)--要横向显示的字段名 答案
,@ColumKeyItemvarchar(50)--要横向显示的字段名的Key字段名 问题编号
,@KeyColumItemvarchar(50)--横向显示的Key字段名 用户编号
,@ColumAsNamevarchar(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_Idint identity(1,1) not null,--序号
c_UserIdint,--用户编号
c_QuestionIdint,--问题编号
c_Itemnvarchar(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
@Tablevarchar(50)--表名
,@ColumNamevarchar(50)--要横向显示的字段名 答案
,@ColumKeyItemvarchar(50)--要横向显示的字段名的Key字段名 问题编号
,@KeyColumItemvarchar(50)--横向显示的Key字段名 用户编号
,@ColumAsNamevarchar(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 server 纵向表横向输出的实现
- SQL Server数据库纵向转横向
- SQL 纵向表转为横向表
- Sql 横向转纵向
- 可以纵向横向滑动的表格实现
- SQL纵向变成横向,横向变成纵向(经典案例添加自己的想法)
- Oracle的表结构:纵向和横向
- Oracle的表结构:纵向和横向
- Oracle的表结构:纵向和横向
- 横向和纵向的数据库分表
- sql语句纵向转横向
- ASP横向输出和纵向输出PDF
- sql查询结果的纵向变横向排列
- SQL 查询结果的纵向变横向排列
- SQL 查询结果的纵向变横向排列
- SQL 查询结果的纵向变横向排列
- [MSSQL]SQL 查询结果的纵向变横向排列
- ASP中如何实现先横向再纵向的排列
- PHP - HTTP_REFERER
- MasterPage
- 百度最近怎么了,先是关了有啊,然后是百度娱乐,最近还听说,把说吧团队解散,你们对此有何看法?
- Android基础篇之ImageSwitcher与Gallery
- 获取pci连接设备信息
- sql server 纵向表横向输出的实现
- assert_param函数的意义
- fstream使用小结
- struts2架构及请求流程分析
- JavaScript获取网页高度宽度
- bash获取特定目录下所有文件的全路径
- 如何用photoshop切图并去掉图片上面的文字
- 变量替换与扩展
- 从对象获取class文件的绝对路径