通用sql行变列

 /*
  1 * Description:对数据表进行行列转换
  2 * Author:llf
  3 * 调用说明：exec SP_RowChangeCol '学生成绩表','姓名','课程','分数','desc',0,0,'where 年

级=''一年级'''
  4 * 该存储过程调用的相关函数：dbo.Get_StrArrayLength(@groupCol,',')；

dbo.dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)
  */
   
  alter proc SP_RowChangeCol
   @tableName varchar(100),    --要转换的数据表名(学生成绩表)
   @groupCol varchar(100),     --分组列(学生姓名)
   @changeCol varchar(100),    --要转换的列(考试科目)
   @countCol varchar(100),     --统计列(成绩)
   @orderby varchar(4),        --转换列的排序规则(升序asc,降序desc)
   @isColSum int,              --是否统计列（0否；1是）
   @isRowSum int,              --是否统计行（0否；1总计行；2小计行）
   @strWhere varchar(max)      --查询条件('where 年级=''一年级''')
   as
   begin
       declare @sqlhead varchar(8000),@sqlend varchar(8000)
        ,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
        ,@i int,@ic varchar(20),@sql varchar(1000)
  
  
       /*****创建临时表******************/
       create table #Temp_Change
       (
           [id] int identity(0,1),
           [gid] int,
           [info] varchar(1000)
       )
       
       /*****生成数据处理临时表**********/
       set @sql='insert into #Temp_Change select gid=0
        ,info='',[''+cast('+@changeCol +' as varchar)+'']=sum(case '+@changeCol+' when 

''''''+cast('
        +@changeCol+' as varchar)+'''''' then '+@countCol+' else 0 end)''
        from(select distinct '+@changeCol+' from '+@tableName+' '+@strWhere+') a order by 

'+@changeCol+' '+@orderby+''
       /*    
       insert into #Temp_Change select gid=0
        ,info=',['+cast(日期 as varchar)+']=sum(case 日期 when '''+cast(日期 as varchar)

+''' then 销售额 else 0 end)'
        from(select distinct 日期 from tb) a
       */
       --print @sql
       exec(@sql)
       --select * from #Temp_Change
  
       /*****判断需要多少个变量来处理*************/
       select @i=max(len(info)) from #Temp_Change
       print @i
       if( @i<>0)--判断数据表@tableName中是否有数据
           set @i=7600/@i
       else 
           return
       --分组临时表
       if( @i<>0)
       update #Temp_Change set gid=id/@i
   
       select @i=max(gid) from #Temp_Change
  
  
       /*****处理多个统计列的情况************/
  
       --获得字符数组的长度
      declare @ArrayLength int  
      set @ArrayLength=dbo.Get_StrArrayLength(@groupCol,',')
  
      declare @next int  
      set @next=1
      select @sqlhead='''select '
      while @next<=@ArrayLength  
      begin 
          IF(@next=@ArrayLength)--最后一个字段
          BEGIN
              select @sqlhead=@sqlhead+'case when grouping('+dbo.Get_StrArrayStrOfIndex

(@groupCol,',',@next)+')=1 then ''''合计'''' 
              else convert(varchar,'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@next)+',120) 

end as '+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@next)+''''
         END
         /*
         ELSE IF(@next=@ArrayLength)
         BEGIN
             select @sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@next)+''''
         END
         */
         ELSE
         BEGIN
             select @sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@next)+','
         END
         set @next=@next+1  
     end
 
      /*****生成数据处理语句**************************/
   
      --处理条件字符串
      set @strWhere=replace(@strWhere,'''','''''');
 
      IF(@isColSum=0 AND @isRowSum=0)   --不统计
      BEGIN
          --select @sqlhead='''select '+@groupCol+'=cast('+@groupCol+' as varchar)'''
          select @sqlhead='''select '+@groupCol+' '''
          ,@sqlend=''' from '+@tableName+' '+@strWhere+' group by '+@groupCol+''''
          ,@sql1='',@sql2='select ',@sql3='',@sql4=''
      END
          
      IF (@isRowSum=0 AND @isColSum=1)--要进行col统计
      BEGIN
          --select @sqlhead='''select '+@groupCol+'=cast('+@groupCol+' as varchar)'''
          select @sqlhead='''select '+@groupCol+' '''
          ,@sqlend=''',sum('+@countCol+') as ''''合计'''' from '+@tableName+' '+@strWhere+' 

group by '+@groupCol+''''
          ,@sql1='',@sql2='select ',@sql3='',@sql4=''
      END
  
      
      IF (@isRowSum=1 AND @isColSum=0)--要进行row总计
      BEGIN
          IF(charIndex(',',@groupCol)=0)--只有一个分组列
          BEGIN
                     /*
                     select  case when grouping(a)=1 then '合计' else cast(a as varchar) 

end a,b,c,sum(d),sum(e) from #t 
                     group by a,b,c with rollup 
                     having grouping(c)=0 or grouping(a)=1
                     */
                 select @sqlhead='''select case when grouping('+@groupCol+')=1 then ''''合

计'''' else cast('+@groupCol+' as varchar) end as '+@groupCol+''''
                  ,@sqlend=''' from '+@tableName+' '+@strWhere+' group by '+@groupCol+' 

with rollup '''
                  ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END
          ELSE
          BEGIN
                  SELECT @sqlend=''' from '+@tableName+' '+@strWhere+' group by 

'+@groupCol+' with rollup having grouping('+dbo.Get_StrArrayStrOfIndex

(@groupCol,',',@ArrayLength)+')=0 
                 or grouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+')=1'''
                  ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END
      END

      IF (@isRowSum=2 AND @isColSum=0)--要进行row小计
      BEGIN
          IF(charIndex(',',@groupCol)=0)--只有一个分组列
          BEGIN
                     /*
                     select  case when grouping(a)=1 then '合计' else cast(a as varchar) 

end a,b,c,sum(d),sum(e) from #t 
                     group by a,b,c with rollup 
                     having grouping(c)=0 or grouping(a)=1
                     */
                 select @sqlhead='''select case when grouping('+@groupCol+')=1 then ''''合

计'''' else cast('+@groupCol+' as varchar) end as '+@groupCol+''''
                  ,@sqlend=''' from '+@tableName+' '+@strWhere+' group by '+@groupCol+' 

with rollup '''
                  ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END
          ELSE
          BEGIN
                    /*
                    Groups=CASE 
                        WHEN GROUPING(Color)=0 THEN Groups
                        WHEN GROUPING(Groups)=1 THEN '总计'
                        ELSE '' END,
                    Item=CASE 
                        WHEN GROUPING(Color)=0 THEN Item
                        WHEN GROUPING(Groups)=0 AND GROUPING(Item)

=1  THEN '合计'
                        ELSE '' END,
                    Color=CASE 
                        WHEN GROUPING(Color)=0 THEN Color
                        WHEN  GROUPING(Item)=0 AND GROUPING(Color)

=1 THEN '小计'
                        ELSE '' END,
                    */
                      select @sqlhead='''select '
                             --第一个字段
                             set 

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+'=case when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 then 

'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+' when grouping('+dbo.Get_StrArrayStrOfIndex

(@groupCol,',',1)+')=1 then ''''总计'''' else '''''''' end,'
                      
                      declare @colIndex int  
                      set @colIndex=2
                      while @colIndex<@ArrayLength  
                      begin 
                             set 

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex)+'=case when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 then 

'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex)+' when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex-1)+')=0 and grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex)+')=1 then ''''合计'''' else '''''''' 

end,'
                             set @colIndex=@colIndex+1  
                      end
                             --最后一个字段
                             set 

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+'=case when 

grouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 then 

'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+' when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength-1)+')=0 and grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=1 then ''''小计'''' else 

'''''''' end'''
                        


                   select @sqlend=''' from '+@tableName+' '+@strWhere+' group by 

'+@groupCol+' with rollup ''',
                          @sql1='',@sql2='select 

',@sql3='',@sql4=''
          END
      END
     
      IF( @isRowSum=1 AND @isColSum=1 ) --要进行行和列统计（行总计）
      BEGIN
          IF(charIndex(',',@groupCol)=0)--只有一个分组列
          BEGIN
              --select @sqlhead='''select '+@groupCol+'=cast(isnull

('+@groupCol+',''''total'''') as varchar)'''
             select @sqlhead='''select case when grouping('+@groupCol+')=1 then ''''合

计'''' else cast('+@groupCol+' as varchar) end as '+@groupCol+''''
              ,@sqlend=''',sum('+@countCol+') as ''''合计'''' from '+@tableName+' 

'+@strWhere+' group by '+@groupCol+' with rollup'''
              ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END
          ELSE
          BEGIN
             SELECT @sqlend=''',sum('+@countCol+') as ''''合计'''' from '+@tableName+' 

'+@strWhere+' group by '+@groupCol+' with rollup having grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 
             or grouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+')=1'''
             ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END 
     END
 
     IF( @isRowSum=2 AND @isColSum=1 ) --要进行行和列统计（行小计）
      BEGIN
          IF(charIndex(',',@groupCol)=0)
          BEGIN
              --select @sqlhead='''select '+@groupCol+'=cast(isnull

('+@groupCol+',''''total'''') as varchar)'''
             select @sqlhead='''select case when grouping('+@groupCol+')=1 then ''''合

计'''' else cast('+@groupCol+' as varchar) end as '+@groupCol+''''
              ,@sqlend=''',sum('+@countCol+') as ''''合计'''' from '+@tableName+' 

'+@strWhere+' group by '+@groupCol+' with rollup'''
              ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END
          ELSE
          BEGIN
                      select @sqlhead='''select '
                             --第一个字段
                             set 

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+'=case when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 then 

'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+' when grouping('+dbo.Get_StrArrayStrOfIndex

(@groupCol,',',1)+')=1 then ''''总计'''' else '''''''' end,'
                      
                      declare @colIndex1 int  
                      set @colIndex1=2
                      while @colIndex1<@ArrayLength  
                      begin 
                             set 

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1)+'=case when 

grouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 then 

'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1)+' when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1-1)+')=0 and grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1)+')=1 then ''''合计'''' else 

'''''''' end,'
                             set @colIndex1=@colIndex1+1  
                      end
                             --最后一个字段
                             set 

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+'=case when 

grouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0 then 

'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+' when grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength-1)+')=0 and grouping

('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=1 then ''''小计'''' else 

'''''''' end'''

             SELECT @sqlend=''',sum('+@countCol+') as ''''合计'''' from '+@tableName+' 

'+@strWhere+' group by '+@groupCol+' with rollup '''
             ,@sql1='',@sql2='select ',@sql3='',@sql4=''
          END 
     END

     while @i>=0
     begin
          select @ic=cast(@i as varchar),@i=@i-1
           ,@sql1='@'+@ic+' varchar(8000),'+@sql1
           ,@sql2=@sql2+'@'+@ic+'='''','
           ,@sql3='select @'+@ic+'=@'+@ic+'+info from #Temp_Change where gid='+@ic
            +char(13)+@sql3
           ,@sql4=@sql4+'+@'+@ic
     end
     
     select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
      ,@sql2=left(@sql2,len(@sql2)-1)+char(13)
      ,@sql3=left(@sql3,len(@sql3)-1)
      ,@sql4=substring(@sql4,2,len(@sql4))
 
     /*****执行***********************/
     
     print  @sql1+@sql2+@sql3+'
     exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
 
     exec( @sql1+@sql2+@sql3+'
     exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
     )
 
     /*****删除临时表**********************/
     drop table #Temp_Change
 
 end

两个辅助函数

CREATE function Get_StrArrayLength  
(  
@str varchar(1024), --要分割的字符串  
@split varchar(10) --分隔符号  
)  
returns int  
as  
begin  
declare @location int  
declare @start int  
declare @length int  

set @str=ltrim(rtrim(@str))  
set @location=charindex(@split,@str)  
set @length=1  
while @location<>0  
begin  
set @start=@location+1  
set @location=charindex(@split,@str,@start)  
set @length=@length+1  
end  
return @length  
end  

 

 

CREATE function Get_StrArrayStrOfIndex  
(  
@str varchar(1024), --要分割的字符串  
@split varchar(10), --分隔符号  
@index int --取第几个元素  
)  
returns varchar(1024)  
as  
begin  
declare @location int  
declare @start int  
declare @next int  
declare @seed int  

set @str=ltrim(rtrim(@str))  
set @start=1  
set @next=1  
set @seed=len(@split)  

set @location=charindex(@split,@str)  
while @location<>0 and @index>@next  
begin  
set @start=@location+@seed  
set @location=charindex(@split,@str,@start)  
set @next=@next+1  
end  
if @location =0 select @location =len(@str)+1  
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。  

return substring(@str,@start,@location-@start)  
end