mssql 高难度行转列

create table pds
(姓名 varchar(8), 
内容 varchar(5), 
开始日期 date, 
结束日期 date
)

insert into pds
select '张三', 'AAA', '2012/3/1', '2010/3/3' union all
select '李四', 'BBB', '2012/3/5', '2012/3/6' union all
select '王五', 'CCC', '2012/3/4', '2012/3/7' union all
select '张三', 'ddd', '2012/3/8', '2012/3/8' union all
select '王五', 'eee', '2012/3/2', '2012/3/3'


declare @sql varchar(6000),@t varchar(2000)

select @t=stuff(
(select ','+d from
(select '['+cast(dateadd(d,number,(select min(开始日期) from pds)) as varchar)+']' d
from master.dbo.spt_values 
where [type]='P' and number<=
(select datediff(d,min(开始日期),max(开始日期)) from pds)) t
for xml path('')),1,1,'')

select @sql=
'with t5 as
(select t3.da,t3.姓名,isnull(t4.内容,'''') 内容
from
(select t1.姓名,t2.da from
(select distinct 姓名 from pds) t1
cross join
(select dateadd(d,number,(select min(开始日期) from pds)) da
from master.dbo.spt_values 
where [type]=''P'' and number<=
(select datediff(d,min(开始日期),max(开始日期)) from pds)
) t2) t3
left join pds t4 on t3.姓名=t4.姓名 and t3.da=t4.开始日期
)
select 姓名,'+@t+
'from t5 pivot(max(内容) for da in('+@t+')) t6'

exec(@sql)
来自:  http://52mvc.com/showtopic-551.aspx