select a.id from tb a left join (select * from tb where num = 0) t1 on a.id = t1.id left join (select * from tb where num = 1) t2 on a.id = t2.id left join (select * from tb where num = -1) t3 on a.id = t3.id
create table a (id varchar(10),num int) insert into a select '01',0 union all select '01',1 union all select '01',-1declare @e varchar(200),@i int select @e='',@i=1 select @e=@e+',max(case when num='''+convert(varchar(10), num)+''' then num else null end) as ''n'+convert(varchar(10),@i)+'''',@i=@i+1 from a print @eexec('select id'+@e+'from a group by id')/* 结果 id n1 n2 n3 -------------------- 01 0 1 -1*/
left join (select * from tb where num = 0) t1 on a.id = t1.id
left join (select * from tb where num = 1) t2 on a.id = t2.id
left join (select * from tb where num = -1) t3 on a.id = t3.id
create table a (id varchar(10),num int)
insert into a
select '01',0
union all select '01',1
union all select '01',-1declare @e varchar(200),@i int
select @e='',@i=1
select @e=@e+',max(case when num='''+convert(varchar(10), num)+''' then num else null end) as ''n'+convert(varchar(10),@i)+'''',@i=@i+1 from a
print @eexec('select id'+@e+'from a group by id')/*
结果
id n1 n2 n3
--------------------
01 0 1 -1*/