For SO answer: http://stackoverflow.com/a/8275771/939860
Q&A for linguists, teachers and students of the Korean language
-- Enter Query Title -- Enter Query Description create table #t (CheckDate date, CheckType nvarchar(3), CheckTime time) Insert into #t values ('2011-11-24', 'OUT', '14:30:11') ,('2011-11-25', 'IN', '14:40:00') ,('2011-11-25', 'OUT', '14:45:00') ,('2011-11-25', 'IN', '14:50:00') ,('2011-11-25', 'OUT', '14:55:00') ,('2011-11-25', 'IN', '15:00:00') ,('2011-11-25', 'OUT', '15:05:00') ,('2011-11-25', 'IN', '15:15:00') ,('2011-11-25', 'OUT', '15:20:00') ,('2011-11-25', 'IN', '15:25:00') ,('2011-11-25', 'IN', '15:26:00') ,('2011-11-25', 'OUT', '15:30:00') ,('2011-11-25', 'OUT', '15:40:00') ,('2011-11-25', 'IN', '15:45:00') ;WITH x AS ( SELECT CheckDate, CheckType, CheckTime ,row_number() OVER (ORDER BY CheckDate, CheckTime) As rn FROM #t ) SELECT CASE WHEN x.CheckType = 'IN' OR x.rn = 1 THEN x.CheckDate ELSE y.CheckDate END AS CheckDate ,CASE WHEN x.CheckType = 'IN' THEN x.CheckTime ELSE NULL END AS CheckIn ,CASE WHEN y.CheckType = 'OUT' THEN y.CheckTime WHEN x.CheckType = 'OUT' THEN x.CheckTime ELSE NULL END AS CheckOut FROM x LEFT JOIN x AS y ON y.rn = x.rn + 1 WHERE x.CheckType = 'IN' OR y.CheckType = 'OUT' OR x.rn = 1