select distinct 科目 into #t from mytable order by 科目 declare @s varchar(3000) set @s = 'select 姓名' select @s = @s + ',sum(case when 科目 ='''+ 科目 + ''' then cast(分数 as numeric(8,1)) else 0 end) as ' +科目 from #t set @s = @s + ' from mytable group by 姓名' exec (@s)
DECLARE @SQLSTR VARCHAR(7999) DECLARE @SQLSTR2 VARCHAR(7999)DECLARE @PSQLSTR VARCHAR(7999) DECLARE @PSQLSTR2 VARCHAR(7999)select distinct 科目 as 科目 into #tmp_nj from mytable SELECT @SQLSTR='select 姓名,' SELECT @SQLSTR2='select 姓名,' SELECT @PSQLSTR='' SELECT @PSQLSTR=@PSQLSTR+' CASE WHEN 科目='''+rtrim(科目)+''' then 分数 else '''' end as "'+rtrim(科目)+'", ' from #tmp_nj SELECT @PSQLSTR2='' SELECT @PSQLSTR2=@PSQLSTR2+'max('+rtrim(科目)+') as "'+rtrim(科目)+'",' from #tmp_nj select @SQLSTR=@SQLSTR+left(rtrim(@PSQLSTR),len(rtrim(@PSQLSTR))-1)+' into #cjwzl from mytable ' select @SQLSTR2=@SQLSTR2+left(rtrim(@PSQLSTR2),len(rtrim(@PSQLSTR2))-1)+' INTO #AAA from #cjwzl group by 姓名 ' select @SQLSTR2=@SQLSTR2+' SELECT * FROM #AAA' print @sqlstr print @sqlstr2 exec (@sqlstr+@sqlstr2) drop table #tmp_nj
得到结果 姓名 化学 历史 数学 体育 英语 语文 政治 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- A 30 10 20 B 40 60 50 C 70 D 90 80 E 88.8 F 100
declare @sql varchar(8000) set @sql = 'select 姓名' select @sql = @sql + ',sum(cast(case 科目 when '''+科目+''' then 分数 end as numeric(10,2))) as ['+科目+']' from (select distinct 科目 from mytable) as a select @sql = @sql+' from mytable group by 姓名'exec(@sql) go
declare @sql varchar(8000) set @sql = 'select 科目' select @sql = @sql + ',sum(cast(case 姓名 when '''+姓名+''' then 分数 end as numeric(10,2))) as ['+姓名+']' from (select distinct 姓名 from mytable) as a select @sql = @sql+' from mytable group by 科目'exec(@sql) go
上面两个,我不知道你要哪个,这样也可以:declare @sql varchar(8000) set @sql = 'select 科目' select @sql = @sql + ',max(case 姓名 when '''+姓名+''' then 分数 end) as ['+姓名+']' from (select distinct 姓名 from mytable) as a select @sql = @sql+' from mytable group by 科目'exec(@sql) go
declare @s varchar(3000)
set @s = 'select 姓名'
select @s = @s + ',sum(case when 科目 ='''+ 科目 + ''' then cast(分数 as numeric(8,1)) else 0 end) as ' +科目 from #t
set @s = @s + ' from mytable group by 姓名'
exec (@s)
DECLARE @SQLSTR VARCHAR(7999)
DECLARE @SQLSTR2 VARCHAR(7999)DECLARE @PSQLSTR VARCHAR(7999)
DECLARE @PSQLSTR2 VARCHAR(7999)select distinct 科目 as 科目 into #tmp_nj from mytable SELECT @SQLSTR='select 姓名,'
SELECT @SQLSTR2='select 姓名,' SELECT @PSQLSTR=''
SELECT @PSQLSTR=@PSQLSTR+' CASE WHEN 科目='''+rtrim(科目)+''' then 分数 else '''' end as "'+rtrim(科目)+'", ' from #tmp_nj
SELECT @PSQLSTR2=''
SELECT @PSQLSTR2=@PSQLSTR2+'max('+rtrim(科目)+') as "'+rtrim(科目)+'",' from #tmp_nj select @SQLSTR=@SQLSTR+left(rtrim(@PSQLSTR),len(rtrim(@PSQLSTR))-1)+' into #cjwzl from mytable '
select @SQLSTR2=@SQLSTR2+left(rtrim(@PSQLSTR2),len(rtrim(@PSQLSTR2))-1)+' INTO #AAA from #cjwzl group by 姓名 '
select @SQLSTR2=@SQLSTR2+' SELECT * FROM #AAA' print @sqlstr
print @sqlstr2
exec (@sqlstr+@sqlstr2) drop table #tmp_nj
姓名 化学 历史 数学 体育 英语 语文 政治
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
A 30 10 20
B 40 60 50
C 70
D 90 80
E 88.8
F 100
set @sql = 'select 姓名'
select @sql = @sql + ',sum(cast(case 科目 when '''+科目+''' then 分数 end as numeric(10,2))) as ['+科目+']'
from (select distinct 科目 from mytable) as a
select @sql = @sql+' from mytable group by 姓名'exec(@sql)
go
set @sql = 'select 科目'
select @sql = @sql + ',sum(cast(case 姓名 when '''+姓名+''' then 分数 end as numeric(10,2))) as ['+姓名+']'
from (select distinct 姓名 from mytable) as a
select @sql = @sql+' from mytable group by 科目'exec(@sql)
go
set @sql = 'select 科目'
select @sql = @sql + ',max(case 姓名 when '''+姓名+''' then 分数 end) as ['+姓名+']'
from (select distinct 姓名 from mytable) as a
select @sql = @sql+' from mytable group by 科目'exec(@sql)
go