if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([datime] varchar(10),[val1] varchar(8),[val2] varchar(8),[val3] varchar(8))
insert [tb]
select '20120521','521日值1','521日值2','521日值3' union all
select '20120522','522日值1','522日值2','522日值3' union all
select '20120523','523日值1','523日值2','523日值3' union all
select '20120524','524日值1','524日值2','524日值3' union all
select '20120525','525日值1','525日值2','525日值3' union all
select '20120622','622日值1','622日值2','622日值3' union all
select '20120623','623日值1','623日值2','623日值3' union all
select '20120624','624日值1','624日值2','624日值3' union all
select '20120525','625日值1','625日值2','625日值3'
go

declare @sql varchar(8000)
select 
  @sql=isnull(@sql+',','')
  +'max(case when right([datime],2)='''+dd+''' then val1 end) as ['+dd+'日]'
from
(select distinct right([datime],2) dd from tb) t

exec ('select left(datime,6) as 年月,'+@sql+' from tb group by left(datime,6)')

/**
年月         21日      22日      23日      24日      25日
---------- -------- -------- -------- -------- --------
201205     521日值1   522日值1   523日值1   524日值1   625日值1
201206     NULL     622日值1   623日值1   624日值1   NULL

(2 行受影响)
**/

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

SQL code

-->TravyLee生成测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[datime] datetime,
[val1] varchar(8),
[val2] varchar(8),
[val3] varchar(8)
)
go
insert [test]
select '20120521','521日值1','521日值2','521日值3' union all
select '20120522','522日值1','522日值2','522日值3' union all
select '20120523','523日值1','523日值2','523日值3' union all
select '20120524','524日值1','524日值2','524日值3' union all
select '20120525','525日值1','525日值2','525日值3' union all
select '20120622','622日值1','622日值2','622日值3' union all
select '20120623','623日值1','623日值2','623日值3' union all
select '20120624','624日值1','624日值2','624日值3' union all
select '20120525','625日值1','625日值2','625日值3'
go
select CONVERT(varchar(6),[datime],112) as [datime] from test

--写个存储过程:
if OBJECT_ID('pro_test') is not null
drop proc pro_test
go
create proc pro_test
(
@Date datetime
)
as
declare @str varchar(max)
set @str=''
select 
    @str=@str+',['+ltrim(DATEPART(DD,[datime]))+'日]=max(case when DATEPART(DD,[datime])='
    +LTRIM(DATEPART(DD,[datime]))+' then [val1] else '''' end)'
from 
    test 
where 
    [datime]<=@Date
group by
    DATEPART(DD,[datime])
set @str='select convert(varchar(6),[datime],112) as [datime]'
         +@str+' from test group by convert(varchar(6),[datime],112)'
exec(@str)
go


exec pro_test '20120525'
go
/*
datime    21日    22日    23日    24日    25日
------------------------------------------------
201205    521日值1    522日值1    523日值1    524日值1    625日值1
201206        622日值1    623日值1    624日值1    
*/