表格如下:id num time
01 2 9:01
02 3 9:10
02 4 9:15
03 5 9:01
03 2 9:22
03 7 9:38希望得到如下结果:id n1 n2 n3
01 2
02 3 4
03 5 2 7
01 2 9:01
02 3 9:10
02 4 9:15
03 5 9:01
03 2 9:22
03 7 9:38希望得到如下结果:id n1 n2 n3
01 2
02 3 4
03 5 2 7
insert into tb values('01', 2, '9:01')
insert into tb values('02', 3, '9:10')
insert into tb values('02', 4, '9:15')
insert into tb values('03', 5, '9:01')
insert into tb values('03', 2, '9:22')
insert into tb values('03', 7, '9:38')
go
--静态SQL,指同一个ID最多三个(或固定多少个)
select id ,
max(case px when 1 then cast(num as varchar) else '' end) 'n1',
max(case px when 2 then cast(num as varchar) else '' end) 'n2',
max(case px when 3 then cast(num as varchar) else '' end) 'n3'
from
(
select * , px = (select count(1) from tb where id = t.id and time < t.time) + 1 from tb t
) t
group by id
order by id
/*
id n1 n2 n3
---------- ------------------------------ ------------------------------ ------------------------------
01 2
02 3 4
03 5 2 7
(3 行受影响)
*/--动态SQL,指同一个ID数量不定,以最多的为准
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then cast(num as varchar) else '''' end) [n' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where id = t.id and time < t.time) + 1 from tb t) m) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where id = t.id and time < t.time) + 1 from tb t) m group by id order by id'
exec(@sql)
/*
id n1 n2 n3
---------- ------------------------------ ------------------------------ ------------------------------
01 2
02 3 4
03 5 2 7
(3 行受影响)
*/drop table tb
insert into tb values('01', 2, '9:01')
insert into tb values('02', 3, '9:10')
insert into tb values('02', 4, '9:15')
insert into tb values('03', 5, '9:01')
insert into tb values('03', 2, '9:22')
insert into tb values('03', 7, '9:38') go
select *,pid = (select count(1) from tb where a.id = id and time < a.time) + 1
into #
from tb a--动态SQL,指同一个ID数量不定,以最多的为准
declare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ' ,[n'+ltrim(pid)+']= max(case pid when ''' + ltrim(pid) + ''' then num else 0 end) '
from (select distinct pid from #) as a
set @sql = @sql + ' from # group by id order by id'
exec(@sql)
drop table tb,#/*id n1 n2 n3
---------- ----------- ----------- -----------
01 2 0 0
02 3 4 0
03 5 2 7
*/