sqlserver中：
/*
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(8000) --查询条件('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(8000)
/*****创建临时表******************/
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(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
*/
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
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(8000) --查询条件('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(8000)
/*****创建临时表******************/
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(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
*/
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
(
@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
(
@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
mysql中：
1
DROP PROCEDURE IF EXISTS `SP_RowChangeCol` ;
2
/*
3
* Description:对数据表进行行列转换
4 * Author:llf
5 * 调用说明：call SP_RowChangeCol('表名','分组字段','转换成列的字段','统计字段','排序字段',是否统计列,是否统计行);
6
*/
7CREATE PROCEDURE `SP_RowChangeCol`(
8 IN $tableName VARCHAR(100), #表名
9 IN $groupCol VARCHAR(100), #分组字段
10 IN $changeCol VARCHAR(100), #转换成列的字段
11 IN $countCol VARCHAR(100), #统计字段
12 IN $orderby VARCHAR(4), #排序字段
13 IN $isColSum INT , #是否统计列（1是；0否）
14 IN $isRowSum INT #是否统计行（1是；0否）
15 )
16label1:BEGIN
17 SET @EE='';
18 set @sqlString= CONCAT(
19 'select @EE:=CONCAT(@EE,'',SUM(case '
20 ,$changeCol
21 ,' when '','''''''',CAST('
22 ,$changeCol
23 ,' AS CHAR),'''''''','' then '
24 ,$countCol
25 ,' else 0 end) as '','''''''',CAST('
26 ,$changeCol
27 ,' AS CHAR),'''''''') from(select distinct '
28 ,$changeCol
29 ,' from '
30 ,$tableName
31 ,') as a order by '
32 ,$changeCol
33 ,' '
34 ,$orderby
35 );
36
37 #select @sqlString;
38 prepare sqlstmt from @sqlString;
39 execute sqlstmt;
40 #select @EE;
41 deallocate prepare sqlstmt;
42 #DROP PREPARE sqlstmt;
43
44 set @QQ='';
45
46 #不进行行列统计
47 IF $isColSum=0 AND $isRowSum=0 THEN
48 SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' FROM ',$tableName,' GROUP BY ',$groupCol);
49 END IF;
50 #要进行列统计
51 IF $isColSum=1 AND $isRowSum=0 THEN
52 SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' ,SUM(',$countCol,') AS ''total'' FROM ',$tableName,' GROUP BY ',$groupCol);
53 END IF;
54 #要进行列统计
55 IF $isRowSum=1 AND $isColSum=0 THEN
56 IF LOCATE(',',$groupCol)=0 THEN
57 SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' FROM ',$tableName,' GROUP BY ',$groupCol,' WITH ROLLUP');
58 ELSE
59 LEAVE label1;
60 END IF;
61 END IF;
62 #要进行行和列统计
63 IF $isRowSum=1 AND $isColSum=1 THEN
64 IF LOCATE(',',$groupCol)=0 THEN
65 SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' ,SUM(',$countCol,') AS ''total'' FROM ',$tableName,' GROUP BY ',$groupCol,' WITH ROLLUP');
66 ELSE
67 LEAVE label1;
68 END IF;
69 END IF;
70
71 #select @QQ;
72 PREPARE stmt FROM @QQ;
73 execute stmt;
74 DEALLOCATE PREPARE stmt;
75END;
76call SP_RowChangeCol('tx','c1','c2','c3','desc',1,1);
77/*该存储过程最终执行的效果和以下语句执行结果一样
78select sname
79,MAX(case sdate when '2006-01-12 00:00:00' then svalue else 0 end) as '2006-01-12 00:00:00'
80,MAX(case sdate when '2006-01-11 00:00:00' then svalue else 0 end) as '2006-01-11 00:00:00'
81,MAX(case sdate when '2006-01-10 00:00:00' then svalue else 0 end) as '2006-01-10 00:00:00'
82,MAX(case sdate when '2006-01-09 00:00:00' then svalue else 0 end) as '2006-01-09 00:00:00'
83,MAX(case sdate when '2006-01-08 00:00:00' then svalue else 0 end) as '2006-01-08 00:00:00'
84,MAX(case sdate when '2006-01-07 00:00:00' then svalue else 0 end) as '2006-01-07 00:00:00'
85,MAX(case sdate when '2006-01-06 00:00:00' then svalue else 0 end) as '2006-01-06 00:00:00'
86,MAX(case sdate when '2006-01-05 00:00:00' then svalue else 0 end) as '2006-01-05 00:00:00'
87,MAX(case sdate when '2006-01-04 00:00:00' then svalue else 0 end) as '2006-01-04 00:00:00'
88,MAX(case sdate when '2006-01-03 00:00:00' then svalue else 0 end) as '2006-01-03 00:00:00'
89,MAX(case sdate when '2006-01-02 00:00:00' then svalue else 0 end) as '2006-01-02 00:00:00'
90FROM tb GROUP BY sname
91*/
DROP PROCEDURE IF EXISTS `SP_RowChangeCol` ;2
/*
3
* Description:对数据表进行行列转换4
* Author:llf5
* 调用说明：call SP_RowChangeCol('表名','分组字段','转换成列的字段','统计字段','排序字段',是否统计列,是否统计行);6
*/7
CREATE PROCEDURE `SP_RowChangeCol`(8
IN $tableName VARCHAR(100), #表名9
IN $groupCol VARCHAR(100), #分组字段10
IN $changeCol VARCHAR(100), #转换成列的字段11
IN $countCol VARCHAR(100), #统计字段12
IN $orderby VARCHAR(4), #排序字段13
IN $isColSum INT , #是否统计列（1是；0否）14
IN $isRowSum INT #是否统计行（1是；0否）15
)16
label1:BEGIN 17
SET @EE='';18
set @sqlString= CONCAT(19
'select @EE:=CONCAT(@EE,'',SUM(case '20
,$changeCol21
,' when '','''''''',CAST('22
,$changeCol23
,' AS CHAR),'''''''','' then '24
,$countCol25
,' else 0 end) as '','''''''',CAST('26
,$changeCol27
,' AS CHAR),'''''''') from(select distinct '28
,$changeCol29
,' from '30
,$tableName31
,') as a order by '32
,$changeCol33
,' '34
,$orderby35
);36
37
#select @sqlString;38
prepare sqlstmt from @sqlString; 39
execute sqlstmt;40
#select @EE;41
deallocate prepare sqlstmt;42
#DROP PREPARE sqlstmt;43
44
set @QQ='';45
46
#不进行行列统计47
IF $isColSum=0 AND $isRowSum=0 THEN48
SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' FROM ',$tableName,' GROUP BY ',$groupCol);49
END IF;50
#要进行列统计51
IF $isColSum=1 AND $isRowSum=0 THEN 52
SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' ,SUM(',$countCol,') AS ''total'' FROM ',$tableName,' GROUP BY ',$groupCol);53
END IF;54
#要进行列统计 55
IF $isRowSum=1 AND $isColSum=0 THEN56
IF LOCATE(',',$groupCol)=0 THEN57
SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' FROM ',$tableName,' GROUP BY ',$groupCol,' WITH ROLLUP');58
ELSE59
LEAVE label1;60
END IF; 61
END IF;62
#要进行行和列统计63
IF $isRowSum=1 AND $isColSum=1 THEN64
IF LOCATE(',',$groupCol)=0 THEN65
SET @QQ=CONCAT('SELECT ',$groupCol,' ',@EE,' ,SUM(',$countCol,') AS ''total'' FROM ',$tableName,' GROUP BY ',$groupCol,' WITH ROLLUP');66
ELSE67
LEAVE label1;68
END IF;69
END IF;70
71
#select @QQ;72
PREPARE stmt FROM @QQ;73
execute stmt;74
DEALLOCATE PREPARE stmt;75
END;76
call SP_RowChangeCol('tx','c1','c2','c3','desc',1,1);77
/*该存储过程最终执行的效果和以下语句执行结果一样78
select sname79
,MAX(case sdate when '2006-01-12 00:00:00' then svalue else 0 end) as '2006-01-12 00:00:00'80
,MAX(case sdate when '2006-01-11 00:00:00' then svalue else 0 end) as '2006-01-11 00:00:00'81
,MAX(case sdate when '2006-01-10 00:00:00' then svalue else 0 end) as '2006-01-10 00:00:00'82
,MAX(case sdate when '2006-01-09 00:00:00' then svalue else 0 end) as '2006-01-09 00:00:00'83
,MAX(case sdate when '2006-01-08 00:00:00' then svalue else 0 end) as '2006-01-08 00:00:00'84
,MAX(case sdate when '2006-01-07 00:00:00' then svalue else 0 end) as '2006-01-07 00:00:00'85
,MAX(case sdate when '2006-01-06 00:00:00' then svalue else 0 end) as '2006-01-06 00:00:00'86
,MAX(case sdate when '2006-01-05 00:00:00' then svalue else 0 end) as '2006-01-05 00:00:00'87
,MAX(case sdate when '2006-01-04 00:00:00' then svalue else 0 end) as '2006-01-04 00:00:00'88
,MAX(case sdate when '2006-01-03 00:00:00' then svalue else 0 end) as '2006-01-03 00:00:00'89
,MAX(case sdate when '2006-01-02 00:00:00' then svalue else 0 end) as '2006-01-02 00:00:00'90
FROM tb GROUP BY sname91
*/
