mysql将行转列sql server_自己写的sqlserver和mysql的行转列通用存储过程

/*1 * Description:对数据表进行行列转换

2 * Author:llf

3 * 调用说明：exec SP_RowChangeCol '学生成绩表','姓名','课程','分数','desc',0,0,'where 年

级=''一年级'''

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

dbo.dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)*/alterprocSP_RowChangeCol@tableNamevarchar(100),--要转换的数据表名(学生成绩表)@groupColvarchar(100),--分组列(学生姓名)@changeColvarchar(100),--要转换的列(考试科目)@countColvarchar(100),--统计列(成绩)@orderbyvarchar(4),--转换列的排序规则(升序asc,降序desc)@isColSumint,--是否统计列(0否；1是)@isRowSumint,--是否统计行(0否；1总计行；2小计行)@strWherevarchar(8000)--查询条件('where 年级=''一年级''')asbegindeclare@sqlheadvarchar(8000),@sqlendvarchar(8000)

,@sql1varchar(8000),@sql2varchar(8000),@sql3varchar(8000),@sql4varchar(8000)

,@iint,@icvarchar(20),@sqlvarchar(8000)/*****创建临时表******************/createtable#Temp_Change

([id]intidentity(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 @sqlexec(@sql)

--select*from#Temp_Change/*****判断需要多少个变量来处理*************/select@i=max(len(info))from#Temp_Changeprint@iif(@i<>0)--判断数据表@tableName中是否有数据set@i=7600/@ielsereturn--分组临时表if(@i<>0)update#Temp_Changesetgid=id/@iselect@i=max(gid)from#Temp_Change/*****处理多个统计列的情况************/--获得字符数组的长度declare@ArrayLengthintset@ArrayLength=dbo.Get_StrArrayLength(@groupCol,',')declare@nextintset@next=1select@sqlhead='''select'while@next<=@ArrayLengthbeginIF(@next=@ArrayLength)--最后一个字段BEGINselect@sqlhead=@sqlhead+'case when grouping('+dbo.Get_StrArrayStrOfIndex

(@groupCol,',',@next)+')=1 then''''合计''''else convert(varchar(200),'+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*/ELSEBEGINselect@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@next)+','ENDset@next=@next+1end/*****生成数据处理语句**************************/--处理条件字符串set@strWhere=replace(@strWhere,'''','''''');IF(@isColSum=0AND@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=''ENDIF(@isRowSum=0AND@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=''ENDIF(@isRowSum=1AND@isColSum=0)--要进行row总计BEGINIF(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=''ENDELSEBEGINSELECT@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=''ENDENDIF(@isRowSum=2AND@isColSum=0)--要进行row小计BEGINIF(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=''ENDELSEBEGIN/*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@colIndexintset@colIndex=2while@colIndex

('+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+1end--最后一个字段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=''ENDENDIF(@isRowSum=1AND@isColSum=1)--要进行行和列统计(行总计)BEGINIF(charIndex(',',@groupCol)=0)--只有一个分组列BEGIN--select @sqlhead='''select '+@groupCol+'=cast(isnull('+@groupCol+',''''total'''')asvarchar)'''select @sqlhead='''selectcasewhengrouping('+@groupCol+')=1then''''合

计''''elsecast('+@groupCol+'asvarchar)endas'+@groupCol+'''',@sqlend=''',sum('+@countCol+')as''''合计''''from'+@tableName+''+@strWhere+'groupby'+@groupCol+'withrollup''',@sql1='',@sql2='select',@sql3='',@sql4=''END

ELSE

BEGIN

SELECT @sqlend=''',sum('+@countCol+')as''''合计''''from'+@tableName+''+@strWhere+'groupby'+@groupCol+'withrolluphavinggrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0orgrouping('+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'''')asvarchar)'''select @sqlhead='''selectcasewhengrouping('+@groupCol+')=1then''''合

计''''elsecast('+@groupCol+'asvarchar)endas'+@groupCol+'''',@sqlend=''',sum('+@countCol+')as''''合计''''from'+@tableName+''+@strWhere+'groupby'+@groupCol+'withrollup''',@sql1='',@sql2='select',@sql3='',@sql4=''END

ELSE

BEGIN

select @sqlhead='''select'--第一个字段

set

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+'=casewhengrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0then'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+'whengrouping('+dbo.Get_StrArrayStrOfIndex

(@groupCol,',',1)+')=1then''''总计''''else''''''''end,'declare @colIndex1 int

set @colIndex1=2

while @colIndex1

begin

set

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1)+'=casewhengrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0then'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1)+'whengrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1-1)+')=0andgrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@colIndex1)+')=1then''''合计''''else''''''''end,'set @colIndex1=@colIndex1+1

end

--最后一个字段

set

@sqlhead=@sqlhead+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+'=casewhengrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=0then'+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+'whengrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength-1)+')=0andgrouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',@ArrayLength)+')=1then''''小计''''else''''''''end'''SELECT @sqlend=''',sum('+@countCol+')as''''合计''''from'+@tableName+''+@strWhere+'groupby'+@groupCol+'withrollup''',@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+'+infofrom#Temp_Changewheregid='+@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