日期:2014-05-18 浏览次数:20750 次
declare @sql varchar(max) set @sql = 'select id' select @sql = @sql + ',sum(case when convert(varchar(10),date,120) = '''+date+''' then isnull(shuliang,0) else 0 end) ['+date+']' from( select convert(varchar(10),date,120) as date from tb group by convert(varchar(10),date,120) )t select @sql = @sql + ' from tb group by id ' exec(@sql)
------解决方案--------------------
declare @sql varchar(8000) set @sql = 'select id ' select @sql = @sql + ' , sum(case date when ''' + convert(varchar(10),date,120) + ''' then shuliang else null end) [' + convert(varchar(10),date,120) + ']' from (select distinct date from tb) as a set @sql = @sql + ' from tb group by id' exec(@sql)
------解决方案--------------------
--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([id] int,[shuliang] int,[date] Datetime) Insert #T select 1,400,'2011-1-1' union all select 1,500,'2011-1-1' union all select 1,200,'2011-1-4' union all select 2,100,'2011-1-5' Go declare @s nvarchar(4000) set @s='' Select @s=@s+N','+quotename(CONVERT(varchar(10),Date,120))+N'=max(case when CONVERT(varchar(10),Date,120)=N'+quotename(CONVERT(varchar(10),Date,120),'''')+N' then shuliang end)' from #T group by date --顯示生成語句 print N'select ID'+@s+N' from #T group by ID' exec(N'select ID'+@s+N' from #T group by ID') go /* ID 2011-01-01 2011-01-04 2011-01-05 1 500 200 NULL 2 NULL NULL 100 */