认为一个比较麻烦的行转列
id   num
01   0
01   1
01   -1

希望得到如下结果
id   n1   n2   n3
01   0   1   -1

今天上午看到的例子我想请问如果
create   table   #a   (id   varchar(10),num   varchar(100))
insert   into   #a
select   '01 ',0
union   all   select   '01 ',1
union   all   select   '01 ',-1
union   all   select   '02 ',5
union   all   select   '02 ',16
union   all   select   '02 ',-8

多条数据要得到这个效果该如何实现呢，就是动态的实现方法，谢谢


------解决方案--------------------
create table a (id varchar(10),num varchar(10))
insert into a
select '01 ',0
union all select '01 ',1
union all select '01 ',-1
union all select '02 ',5
union all select '02 ',16
union all select '02 ',-8
go

--如果都只有三个数
select id,
max(case px when 1 then num else ' ' end) n1,
max(case px when 2 then num else ' ' end) n2,
max(case px when 3 then num else ' ' end) n3
from
(
select px=(select count(1) from a where id=t.id and num <t.num)+1 , * from a t
) t
group by id

drop table a

/*
id n1 n2 n3
---------- ---------- ---------- ----------
01 0 1 -1
02 16 5 -8

（所影响的行数为 2 行）
*/
------解决方案--------------------
create table #a (id varchar(10),num varchar(100))
insert into #a
select '01 ',0
union all select '01 ',1
union all select '01 ',-1
union all select '02 ',5
union all select '02 ',16
union all select '02 ',-8


--我想要我表中原始的顺序从左到右的排，有点较真。。。可我就是这样想的，谢谢
--=============
--是要借助临时表#b

select iden=identity(int,1,1),* into #b from #a

declare @T varchar(10),@Max int,@sql varchar(8000)
select top 1 @Max=count(1),@T=1 from #b group by id order by count(1) desc
while @T <=@Max select @sql=coalesce(@sql+ ', ', 'select id, ')+ '[n '+@T+ ']=max(case cn when '+@T+ ' then num end) ',@T=@T+1

set @sql=@sql+ ' from (select id,num,cn=(select count(1) from #b where id=a.id and iden <=a.iden) from #b a) a group by id '

exec(@sql)

/*
id n1 n2 n3
01 0 1 -1
02 5 16 -8
*/

drop table #a,#b

------解决方案--------------------
create table #a (id varchar(10),num varchar(100))
insert into #a
select '01 ',0
union all select '01 ',1
union all select '01 ',-1
union all select '02 ',5
union all select '02 ',16
union all select '02 ',-8

select *,i=identity(int,0,1) into #x from #a

select id,
a=max(case when i%3=0 then num end),
b=max(case when i%3=1 then num end),
c=max(case when i%3=2 then num end)
from #x
group by id

drop table #a,#x
------解决方案--------------------
select *,identity(int,1,1) as tt into # from #a
select distinct id into #b from #a order by id
declare @i int,@sql varchar(1000)
set @i=1
while (select count(1) from #) <> 0
begin
select @sql= 'alter table #b add n '+convert(varchar(1