EmplID CardNo RecDate RecTime
00000001 00001 2007-08-18 14:32
00000001 00001 2007-08-18 14:34
00000001 00001 2007-08-18 15:14
00000001 00001 2007-08-18 15:33编排成:EmplID CardNo RecDate RecTime RecTime1 RecTime2 RecTime3
00000001 00001 2007-08-18 14:32 14:34 15:14 15:33这个怎么做呀?大侠!
00000001 00001 2007-08-18 14:32
00000001 00001 2007-08-18 14:34
00000001 00001 2007-08-18 15:14
00000001 00001 2007-08-18 15:33编排成:EmplID CardNo RecDate RecTime RecTime1 RecTime2 RecTime3
00000001 00001 2007-08-18 14:32 14:34 15:14 15:33这个怎么做呀?大侠!
max(case px when 1 then convert(varchar(8),RecDate,114) else '' end) RecTime ,
max(case px when 2 then convert(varchar(8),RecDate,114) else '' end) RecTime1 ,
max(case px when 3 then convert(varchar(8),RecDate,114) else '' end) RecTime2 ,
max(case px when 4 then convert(varchar(8),RecDate,114) else '' end) RecTime3
from
(
select * , px = (select count(1) from tb where EmplID = t.EmplID and CardNo = t.CardNo and convert(varchar(10),RecDate,120) = convert(varchar(10),t.RecDate,120) and convert(varchar(8),RecDate,114) = convert(varchar(8),t.RecDate,114) ) + 1 from tb t
) m
group by EmplID,CardNo, convert(varchar(10),RecDate,120)