--sql2000 select card, max(case when rq='01' then sktime else '' end) as [01], max(case when rq='02' then sktime else '' end) as [02], max(case when rq='03' then sktime else '' end) as [03], max(case when rq='04' then sktime else '' end) as [04] from ( select *,rn=(select count(1)+1 from ta where card=t.card and rq=t.rq and sktime<t.sktime) from ta t ) t group by card,rn/** card 01 02 03 04 ---------- ----- ----- ----- ----- 123456 00:01 01:01 00:01 234567 00:01 123456 00:02 02:01 234567 00:02 123456 00:03 (5 行受影响)**/
--sql2000 select card, max(case when rn=1 then sktime else '' end) as [01], max(case when rn=2 then sktime else '' end) as [02], max(case when rn=3 then sktime else '' end) as [03], max(case when rn=4 then sktime else '' end) as [04] from ( select *,rn=(select count(1)+1 from ta where card=t.card and rq=t.rq and sktime<t.sktime) from ta t ) t group by card,rq/** card 01 02 03 04 ---------- ----- ----- ----- ----- 123456 00:01 00:02 00:03 234567 00:01 00:02 123456 01:01 02:01 123456 00:01 (4 行受影响)**/
IF OBJECT_ID('ta') IS NOT NULL DROP TABLE [ta] GO create table ta(card varchar(10),rq varchar(2),sktime varchar(5)) go insert into ta(card,rq,sktime) select '123456','01','00:01' union select '123456','01','00:02' union select '123456','01','00:03' union select '123456','02','01:01' union select '123456','02','02:01' union select '123456','04','00:01' union select '234567','01','00:01' union select '234567','01','00:02'go declare @s varchar(8000) select @s = isnull(@s,'select card') +',max( case rq when '''+ltrim(rq)+''' then sktime else '''' end ) '+quotename(rq) from (select rq = right(101+number,2) from master..spt_values where type='p' and number<=(select max(cast(rq as int))-min(cast(rq as int)) from ta))t exec(@s+' from (select *,rn = (select count(1) from ta where card = t.card and rq = t.rq and sktime <= t.sktime) from ta t) t group by rn,card order by 1') /* card 01 02 03 04 ---------- ----- ----- ----- ----- 123456 00:01 01:01 00:01 123456 00:02 02:01 123456 00:03 234567 00:01 234567 00:02 (5 行受影响) */
select card,
max(case when rq='01' then sktime else '' end) as [01],
max(case when rq='02' then sktime else '' end) as [02],
max(case when rq='03' then sktime else '' end) as [03],
max(case when rq='04' then sktime else '' end) as [04]
from (
select *,rn=(select count(1)+1 from ta where card=t.card and rq=t.rq and sktime<t.sktime) from ta t
) t
group by card,rn/**
card 01 02 03 04
---------- ----- ----- ----- -----
123456 00:01 01:01 00:01
234567 00:01
123456 00:02 02:01
234567 00:02
123456 00:03 (5 行受影响)**/
123456 00:01 01:01 00:01
123456 00:02 02:01
123456 00:03
234567 00:01
234567 00:02
select card,
max(case when rn=1 then sktime else '' end) as [01],
max(case when rn=2 then sktime else '' end) as [02],
max(case when rn=3 then sktime else '' end) as [03],
max(case when rn=4 then sktime else '' end) as [04]
from (
select *,rn=(select count(1)+1 from ta where card=t.card and rq=t.rq and sktime<t.sktime) from ta t
) t
group by card,rq/**
card 01 02 03 04
---------- ----- ----- ----- -----
123456 00:01 00:02 00:03
234567 00:01 00:02
123456 01:01 02:01
123456 00:01 (4 行受影响)**/
DROP TABLE [ta]
GO
create table ta(card varchar(10),rq varchar(2),sktime varchar(5))
go
insert into ta(card,rq,sktime)
select '123456','01','00:01'
union
select '123456','01','00:02'
union
select '123456','01','00:03'
union
select '123456','02','01:01'
union
select '123456','02','02:01'
union
select '123456','04','00:01'
union
select '234567','01','00:01'
union
select '234567','01','00:02'go
declare @s varchar(8000)
select @s = isnull(@s,'select card')
+',max( case rq when '''+ltrim(rq)+''' then sktime else '''' end ) '+quotename(rq)
from (select rq = right(101+number,2) from master..spt_values where type='p' and number<=(select max(cast(rq as int))-min(cast(rq as int)) from ta))t
exec(@s+' from (select *,rn = (select count(1) from ta where card = t.card and rq = t.rq and sktime <= t.sktime)
from ta t) t group by rn,card order by 1')
/*
card 01 02 03 04
---------- ----- ----- ----- -----
123456 00:01 01:01 00:01
123456 00:02 02:01
123456 00:03
234567 00:01
234567 00:02 (5 行受影响)
*/