;WITH a([name],rq,timeserial) AS (
SELECT N'name1','2016-09-01','8:20 11:20 18:54' union all
SELECT N'name2','2016-09-01','8:10 11:40 19:20' union all
SELECT N'name3','2016-09-01','8:20 12:10 18:34' union all
SELECT N'name4','2016-09-01','8:29 12:20 18:34 22:02'
)
SELECT * FROM (
SELECT a.name,a.rq
, CASE WHEN d.id=1 AND d.checkin>'7:30' AND d.checkin<='8:30' THEN d.checkin
WHEN d.id=2 AND d.checkin>'11:30' AND d.checkin<='12:30' THEN d.checkin
WHEN d.id IN(3,4) THEN d.checkin
ELSE '' END AS checkin
,CASE d.ID WHEN 1 THEN N'上班一' WHEN 2 THEN N'下班一' WHEN 3 THEN N'上班二' WHEN 4 THEN N'下班二' else N'其他' end as duty
FROM a
CROSS APPLY(SELECT a1.*,CONVERT(XML,'<n>'+REPLACE(a1.timeserial,' ','</n><n>')+'</n>') xtime FROM a AS a1 WHERE a1.name=a.name AND a1.rq=a.rq ) b
CROSS APPLY(SELECT ROW_NUMBER()OVER(PARTITION BY a.name,a.rq ORDER BY convert(int,replace(s.c.value('.','varchar(100)'),':',''))) AS ID
, s.c.value('.','varchar(100)') AS checkin FROM b.xtime.nodes('n')s(c)) d
) AS t
PIVOT(MAX(checkin) for duty IN (上班一,下班一,上班二,下班二,其他)) m
name rq 上班一 下班一 上班二 下班二 其他
name1 2016-09-01 8:20 18:54 NULL NULL
name2 2016-09-01 8:10 11:40 19:20 NULL NULL
name3 2016-09-01 8:20 12:10 18:34 NULL NULL
name4 2016-09-01 8:29 12:20 18:34 22:02 NULL