declare @sql varchar(max)
set @sql = 'select Props'
select @sql = @sql + ',sum(case convert(varchar(8),PropTime,112) when '''+date+''' then PropsCoun else 0 end) ['+date+']'
from(
    select convert(varchar(8),PropTime,112) date
    from Hong_Props
    group by convert(varchar(8),PropTime,112)
) t
select @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props '
exec(@sql)

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

SQL code

create table Hong_Props(
PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int
)
insert into Hong_Props
select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all
select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all
select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all
select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all
select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all
select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all
select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all
select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20
go

declare @sql varchar(max)
set @sql = 'select Props'
select @sql = @sql + ',sum(case convert(varchar(10),PropTime,120) when '''+date+''' then PropsCoun else 0 end) ['+date+']'
from(
    select convert(varchar(10),PropTime,120) date
    from Hong_Props
    group by convert(varchar(10),PropTime,120)
) t
order by date
select @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props '
exec(@sql)

drop table Hong_Props

/*************************

Props                2012-02-11  2012-02-12  2012-02-13  2012-02-14  2012-02-15  累计
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
道具A                  24          0           0           0           20          44
道具B                  15          0           0           9           0           24
道具C                  0           14          0           0           0           14
道具D                  0           2           50          0           0           52
道具E                  0           0           0           0           10          10

(5 行受影响)

------解决方案--------------------
SQL code
--如果PropTime字段为字符串型
declare @sql varchar(8000)
set @sql = 'select Props '
select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']'
from (select distinct PropTime from Hong_Props) as a
set @sql =