网上查看行转列.差不多都是对于一个表.今天有时间自己整理下.

-- ============================================= 

-- Author:      xin_zhou 

-- Create date: 2010-03-21 

-- Description: 相对通用行转列 

-- ============================================= 

ALTER PROCEDURE [dbo].[RowToColumn]  

    @tablename nvarchar(50), 

    @group_value nvarchar(50), 

    @coulmn_case nvarchar(50), 

    @coulmn_value nvarchar(50), 

    @defaultvalue nvarchar(50)=null, 

    @group_fun nvarchar(10)='max'

AS

BEGIN

    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 + ' , '

    print @str_head 

    set @str_case = ''

    if(@defaultvalue is null) 

        set @str_default = ' else null end) as '

    else

        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) 

    print @str_case 

    set @str_tail = ' from ' + @tablename + ' group by ' + @group_value 

    exec(@str_head+@str_case+@str_tail) 

END

整个就是差不多如此.参数意思大家对比下面的图就知道了.至于为什么默认的聚合函数为max,是因为行转列应做到一对一.所以聚合函数在这本没有意义.

而别的聚合函数有的只能对int等来取.所以我首看聚合函数能用的范围大就用那个.

有理解错误的地方请大家指正.

ITPub博客

SQL相对通用行转列.

最新文章