试试这个:

SELECT ISNULL(MAX(CASE WHEN Status = 'In' THEN Location END),'') AS LocationIn,
       ISNULL(MAX(CASE WHEN Status = 'Out' THEN Location END),'') AS LocationOut,
       date,
       ISNULL(MAX(CASE WHEN Status = 'In' THEN time END),'') AS TimeIn,
       ISNULL(MAX(CASE WHEN Status = 'Out' THEN time END),'') AS TimeOut
FROM TableName
GROUP BY date

结果:

LOCATIONIN  LOCATIONOUT   DATE                          TIMEIN   TIMEOUT
xyz         abc           June, 06 2014 00:00:00+0000   10:55    4:30
mno         mop           June, 07 2014 00:00:00+0000   11:00    4:00
abc                       June, 08 2014 00:00:00+0000   11:00   

小提琴示例知识库>