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)

------解决方案--------------------
SQL code
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)

------解决方案--------------------

SQL code
--> --> (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
*/