如何通过SQLSERVER语句得到如下结果。

Question

Answer 1

我的方法有点繁琐，每一步的操作都用临时表装起来了，你可以以此执行查看每一个临时表来看懂整个操作的逻辑。我能力有限，这不一定是tsql的最优解，可以参考下

IF OBJECT_ID('TEMPDB..#Tab1') IS NOT NULL
DROP TABLE #Tab1
select * into #Tab1 from (
select sfcno = 10, time = '08:00' 
union all
select sfcno = 10, time = '08:10' 
union all
select sfcno = 11, time = '08:30' 
union all
select sfcno = 22, time = '09:10' 
union all
select sfcno = 33, time = '10:10' 
union all
select sfcno = 44, time = '11:10' 
union all
select sfcno = 555, time = '11:30' 

) as Tab1

IF OBJECT_ID('TEMPDB..#Tab2') IS NOT NULL
DROP TABLE #Tab2
select * into #Tab2 from (
select 
time = case when left(time,1)=0 then substring(time,2,1)+':00-'+cast(substring(time,2,1)+1 as nvarchar)+':00' 
else substring(time,1,2)+':00-'+cast(substring(time,1,2)+1 as nvarchar)+':00' end
,sfcno
from 
#Tab1
group by 
case when left(time,1)=0 then substring(time,2,1)+':00-'+cast(substring(time,2,1)+1 as nvarchar)+':00' 
else substring(time,1,2)+':00-'+cast(substring(time,1,2)+1 as nvarchar)+':00' end
,sfcno
) as Tab2

IF OBJECT_ID('TEMPDB..#Tab3') IS NOT NULL
DROP TABLE #Tab3
select * into #Tab3 from (
select
time
,sfcno = (Select cast(a2.sfcno as varchar)+',' from #Tab2 as a2 where a2.time = a1.time for xml path('')) 
from
#Tab2 as a1  
group by 
time
) as Tab3

select * from #Tab3 pivot (max(sfcno) for time in ([8:00-9:00],[9:00-10:00],[10:00-11:00],[11:00-12:00])) b

最后执行结果比较贴近。可以利用substring与len函数来消除末尾多余的逗号，这里就不继续下去了。如果需要代码我可以继续贴上去。