--来个动态的
create table tb(姓名 varchar(10),数量 varchar(10),日期 varchar(10))
insert into tb select '张三',100,'2006-04-01'
union all select '李四',1000,'2006-04-01'
union all select '张三',500,'2006-04-02'
union all select '李四',1500,'2006-04-02'
declare @sql varchar(8000)
set @sql='select 姓名'
select @sql=@sql+',['+cast(month(日期) as varchar)+'-'+cast(day(日期) as varchar)+']=max(case 日期 when '''+日期+''' then 数量 else 0 end)' from tb group by 日期
print @sql
exec(@sql+' from tb group by 姓名')
declare @s varchar(1000)
select @s = ''
select @s = @s + ', max(case convert(varchar(10),date,120) when ''' + convert(varchar(10),date,120) + ''' then qty end) as ''' +substring(convert(varchar(10),date,120),6,10) + '的数量'''
from #
group by date
select @s = 'select name ' + @s + ' from # group by name'
exec(@s)
select name ,
max(case convert(varchar(10),date,112) when '20060401' then qty end ) as '4-1的数量',
max(case convert(varchar(10),date,112) when '20060402' then qty end ) as '4-2的数量'
from #
group by name