表 A idbo dd Sdate stime
000008 1 2007/09/26 08:00:00
000008 1 2007/09/26 12:30:00
000008 1 2007/09/26 13:30:00
000008 1 2007/09/26 17:00:00
000011 1 2007/09/26 07:30:00
000011 1 2007/09/26 11:30:00
000011 1 2007/09/26 11:45:00
000011 1 2007/09/26 16:30:00
000011 1 2007/09/26 16:45:00
000011 1 2007/09/26 21:00:00
000008 1 2007/09/27 08:20:07
000008 1 2007/09/27 12:20:00
000008 1 2007/09/27 13:00:00
000008 1 2007/09/27 17:30:00
得到查询结果
idno Sdate T1 T2 T3 T4 t5 T6 T7 T8
000008 2007/09/26 08:00:00 12:30:00 13:30:00 17:00:00
000011 2007/09/26 07:30:00 11:30:00 11:45:00 16:30:00 16:45:00 21:00:00
000008 2007/09/27 08:20:07 12:20:00 13:00:00 17:30:00
------解决方案--------------------
declare @sql varchar(8000)
set @sql = 'select idno,Sdate '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then stime else ' ' ' ' end) [T ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from a where idbo=t.idbo and Sdate = t.Sdate and stime <t.stime)+1 , * from a t) m) as a
set @sql = @sql + ' from (select px=(select count(1) from a where idbo=t.idbo and Sdate = t.Sdate and stime <t.stime)+1 , * from a t) m group by idno,Sdate '
exec(@sql)
------解决方案--------------------
create table tb(idbo varchar(10),dd int,Sdate varchar(10),stime varchar(10))
insert into tb values( '000008 ', 1, '2007/09/26 ', '08:00:00 ')
insert into tb values( '000008 ', 1, '2007/09/26 ', '12:30:00 ')
insert into tb values( '000008 ', 1, '2007/09/26 ', '13:30:00 ')
insert into tb values( '000008 ', 1, '2007/09/26 ', '17:00:00 ')
insert into tb values( '000011 ', 1, '2007/09/26 ', '07:30:00 ')
insert into tb values( '000011 ', 1, '2007/09/26 ', '11:30:00 ')
insert into tb values( '000011 ', 1, '2007/09/26 ', '11:45:00 ')
insert into tb values( '000011 ', 1, '2007/09/26 ', '16:30:00 ')
insert into tb values( '000011 ', 1, '2007/09/26 ', '16:45:00 ')
insert into tb values( '000011 ', 1, '2007/09/26 ', '21:00:00 ')