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)+')=0or grouping('+dbo.Get_StrArrayStrOfIndex(@groupCol,',',1)+')=1'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
END
ENDIF (@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)+')=0or 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=
,@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
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*/