create table #t (sid int,sdate varchar(8),stext int) insert #t select 1,'20070625',2 union all select 2,'20070626',3 union all select 3,'20070627',4 union all select 4,'20070628',5 declare @sql varchar(8000) set @sql = '' select @sql = @sql +',a' +sdate + '= sum (case sdate when ''' + sdate + ''' then stext else 0 end )' from #t group by sdate set @sql = stuff(@sql,1,1,'') set @sql = 'select ' + @sql +' from #t' exec(@sql) drop table #t
insert #t
select 1,'20070625',2 union all
select 2,'20070626',3 union all
select 3,'20070627',4 union all
select 4,'20070628',5
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql +',a' +sdate + '= sum (case sdate when ''' + sdate + ''' then stext else 0 end )' from #t group by sdate
set @sql = stuff(@sql,1,1,'')
set @sql = 'select ' + @sql +' from #t'
exec(@sql)
drop table #t
----------- ----------- ----------- -----------
2 3 4 5