求助，纵转横及合并问题
求助，纵表如下，日期，时间段，姓名

日期 时间段 姓名
20130508 8:00-9:00 张三
20130508 9:00-10:00 测试
20130509 8:00-9:00 李四
20130509 8:00-9:00 再测
20130509 9:00-10:00 再测1
....... .......... ....
....... .......... ....
....... .......... ....
....... .......... ....


如何统计按时间段统计 不同日期的人员，相同日期同一时间段的人员还需要合并显示


时间段 日期1 日期2 日期3
8:00-9:00 张三 李四，再测 XX
9:00-10:00 测试 再测1
10:00-11:00
....... ..... ....... ......


[解决办法]

if exists(select 1 from sys.tables where name='test20130507')
drop table test20130507
go
create table test20130507(a varchar(20),b varchar(20),c varchar(20))
insert into test20130507
select '20130508','8:00~9:00','张三' union all
select '20130508','9:00~10:00','测试' union all
select '20130509','8:00~9:00','李四' union all
select '20130509','8:00~9:00','再测' union all
select '20130509','9:00~10:00','再测1' union all
select '20130609','9:00~10:00','再测1'
go
declare @sql varchar(max)=''
select @sql=@sql+',max(case when a='''+a+''' then c else null end)as '''+a+''''  
from (select distinct a from test20130507)a
exec ('select b'+@sql+' 
from (select distinct a,b,
c=stuff((select '',''+c from test20130507 where a.a=a and a.b=b for xml path('''')),1,1,'''')  
from test20130507 a)a group by b')

[解决办法]
if exists(select * from sysobjects where name='e')
drop table e
go
create table e
(
id int primary key identity(1,1),
date nvarchar(20),
atime nvarchar(20),
aname nvarchar(20)
)

go
insert into e
select '20130508','8:00-9:00','张三' union all
select '20130508','9:00-10:00','测试' union all
select '20130509','8:00-9:00','李四' union all
select '20130509','8:00-9:00','再测' union all
select '20130509','9:00-10:00','再测1'
go
with t as
(
select date,atime,
stuff((select ','+aname from e where e.date = e1.date and e.atime = e1.atime for xml path('')),1,1,'')aname2 from e e1
group by date,atime
)
select atime,
max((case when date = '20130508' then aname2 end))date1,
max((case when date = '20130509' then aname2 end))date2
--有的话继续写
from t group by atime