表格如下:
id time num
01 9:01 12
01 9:02 3
01 9:03 99
01 9:04 21
02 9:00 9
02 10:00 100
03 9:11 11
03 9:12 44
03 9:32 15
03 10:00 88
... ... ...
希望得到如下结果
id n1 n2 n3 n4 n5 n6
01 12 3 99 21
02 9 100
03 11 44 15 88
.. ... ...最多六列n1~n6
id time num
01 9:01 12
01 9:02 3
01 9:03 99
01 9:04 21
02 9:00 9
02 10:00 100
03 9:11 11
03 9:12 44
03 9:32 15
03 10:00 88
... ... ...
希望得到如下结果
id n1 n2 n3 n4 n5 n6
01 12 3 99 21
02 9 100
03 11 44 15 88
.. ... ...最多六列n1~n6
insert into tb
select '01', '09:01', 12 union all
select '01', '09:02', 3 union all
select '01', '09:03', 99 union all
select '01', '09:04', 21 union all
select '02', '09:00', 9 union all
select '02', '10:00', 100 union all
select '03', '09:11', 11 union all
select '03', '09:12', 44 union all
select '03', '09:32', 15 union all
select '03', '10:00', 88 select id,
n1=max(case when cid=1 then num else null end ),
n2=max(case when cid=2 then num else null end ),
n3=max(case when cid=3 then num else null end ),
n4=max(case when cid=4 then num else null end ),
n5=max(case when cid=5 then num else null end ),
n6=max(case when cid=6 then num else null end )
from
(select id,(select count(1) from tb where id=a.id and time<=a.time) as cid ,time,num from tb a) tmp
group by id--结果:
/*
id n1 n2 n3 n4 n5 n6
------------------------------------------------------------------------------------------------
01 12 3 99 21 NULL NULL
02 9 100 NULL NULL NULL NULL
03 11 44 15 88 NULL NULL
*/drop table tb
insert into tb
select '01', '09:01', 12 union all
select '01', '09:02', 3 union all
select '01', '09:03', 99 union all
select '01', '09:04', 21 union all
select '02', '09:00', 9 union all
select '02', '10:00', 100 union all
select '03', '09:11', 11 union all
select '03', '09:12', 44 union all
select '03', '09:32', 15 union all
select '03', '10:00', 88 select id,
n1=max(case when cid=1 then num else null end ),
n2=max(case when cid=2 then num else null end ),
n3=max(case when cid=3 then num else null end ),
n4=max(case when cid=4 then num else null end ),
n5=max(case when cid=5 then num else null end ),
n6=max(case when cid=6 then num else null end )
from
(select id,(select count(1) from tb where id=a.id and time<=a.time) as cid ,time,num from tb a) tmp
group by id--结果:
/*
id n1 n2 n3 n4 n5 n6
------------------------------------------------------------------------------------------------
01 12 3 99 21 NULL NULL
02 9 100 NULL NULL NULL NULL
03 11 44 15 88 NULL NULL
*/drop table tb
create table tb(id varchar(2),time char(5), num int)
insert into tb
select '01', '09:01', 12 union all
select '01', '09:02', 3 union all
select '01', '09:03', 99 union all
select '01', '09:04', 21 union all
select '02', '09:00', 9 union all
select '02', '10:00', 100 union all
select '03', '09:11', 11 union all
select '03', '09:12', 44 union all
select '03', '09:32', 15 union all
select '03', '10:00', 88 godeclare @i int,@s nvarchar(2000)
select @i=6,@s=''while @i>0
select @s=',[N'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then rtrim(num) else '''' end)'+@s,@i=@i-1set @s='select ID'+@s+' from (select *,con=(select count(1) from tb where ID=t.ID and time<=t.time) from tb t)tmp group by ID'--print @s--显示动态生成语句exec(@s)
ID N1 N2 N3 N4 N5 N6
---- ------------ ------------ ------------ ------------ ------------ ------------
01 12 3 99 21
02 9 100
03 11 44 15 88