网上查看行转列.差不多都是对于一个表.今天有时间自己整理下.
-- ============================================= |
-- Create date: 2010-03-21 |
-- ============================================= |
ALTER PROCEDURE [dbo].[RowToColumn] |
@group_value nvarchar(50), |
@coulmn_case nvarchar(50), |
@coulmn_value nvarchar(50), |
@defaultvalue nvarchar(50)=null, |
@group_fun nvarchar(10)='max' |
declare @coulmntable table (coulmn nvarchar(50)) |
declare @str_coulmntable nvarchar(120) |
declare @str_head nvarchar(60) |
declare @str_tail nvarchar(120) |
declare @str_case nvarchar(4000) |
declare @str_default nvarchar(60) |
set @str_coulmntable = 'select distinct ' + @coulmn_case + ' as coulmn from ' + @tablename |
insert into @coulmntable exec(@str_coulmntable) |
set @str_head = 'select '+ @group_value + ' , ' |
if(@defaultvalue is null) |
set @str_default = ' else null end) as ' |
set @str_default = ' else ''' + @defaultvalue + ''' end) as ' |
select @str_case = @str_case + ' ' + @group_fun + '(case '+ @coulmn_case + ' when ''' + coulmn + ''' then ' |
+ @coulmn_value + @str_default + coulmn + ' , ' from @coulmntable |
set @str_case = left(@str_case,len(@str_case)-1) |
set @str_tail = ' from ' + @tablename + ' group by ' + @group_value |
exec(@str_head+@str_case+@str_tail) |
整个就是差不多如此.参数意思大家对比下面的图就知道了.至于为什么默认的聚合函数为max,是因为行转列应做到一对一.所以聚合函数在这本没有意义.
而别的聚合函数有的只能对int等来取.所以我首看聚合函数能用的范围大就用那个.
![2010032311245812.png](http://pic002.cnblogs.com/img/zhxi55/201003/2010032311245812.png)
![2010032311250987.png](http://pic002.cnblogs.com/img/zhxi55/201003/2010032311250987.png)
![2010032311251763.png](http://pic002.cnblogs.com/img/zhxi55/201003/2010032311251763.png)
有理解错误的地方请大家指正.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-630177/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-630177/