declare @sql varchar(8000)
set @sql = 'select 时间'
select @sql = @sql + ',max(case 姓名 when '''+姓名+''' then 得分 end) ['+姓名+']'
from test select @sql = @sql+' from test group by 时间'
print @sql
exec(@sql)
drop table test
set @sql = 'select 时间'
select @sql = @sql + ',max(case 姓名 when '''+姓名+''' then 得分 end) ['+姓名+']'
from test select @sql = @sql+' from test group by 时间'
print @sql
exec(@sql)
drop table test
declare @sql varchar(8000)
set @sql = 'select 时间'
select @sql = @sql + ',max(case 姓名 when '''+姓名+''' then 得分 end) ['+姓名+']'
from (select distinct 姓名 from #test ) a select @sql = @sql+' from #test group by 时间'
print @sql
exec(@sql)
--结果
时间 jjjj qqqq zzzz
---------- ----------- ----------- -----------
200302 NULL 10 20
200303 10 15 20
insert into #test select '200302' , 'qqqq' , 10 union all select
'200302' , 'zzzz' , 20 union all select
'200303' , 'qqqq' , 15 union all select
'200303' , 'zzzz' , 20 union all select
'200303' , 'jjjj', 10
declare @sql varchar(8000)
set @sql = 'select 时间'
select @sql = @sql + ',max(case 姓名 when '''+姓名+''' then 得分 end) ['+姓名+']'
from (select distinct 姓名 from #test ) a select @sql = @sql+' from #test group by 时间'
print @sql
exec(@sql)
--结果
时间 jjjj qqqq zzzz
---------- ----------- ----------- -----------
200302 NULL 10 20
200303 10 15 20