高手帮忙：在无法确定字段数目情况下如何将多行数据合并为一行

[wpwpwp_]

表1
ID NAME NUMBER
01 AAA 123
01 BBB 456
02 CCC 456
如何生成表2
ID NAME NUMBER NAME NUMBER
01 AAA 123 BBB 456
02 CCC 456 NULL NULL
想把表一中ID相同得条目合并到一行。空得自动补空或者空着。因为每个ID合并后得字段不一样长，所以不知道该怎么写。

[playwarcraft]

create table T(id varchar(02),name varchar(10),number int)
insert into T select '01','aaa',123
insert into T select '01','bbb',456
insert into T select '02','ccc',456
insert into T select '03','ddd',567
insert into T select '03','eee',678
insert into T select '03','fff',789


select tmp=(select count(*) from T where id=a.id and name<=a.name),*
into # 
from T a
order by id

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when tmp='+rtrim(tmp)+' then name end) as [name'+rtrim(tmp)+'],max(case when tmp='+rtrim(tmp)+' then number end) as [number'+rtrim(tmp)+']' 
from #
group by tmp

select @sql='select id'+@sql+' from # group by id'

exec(@sql)
/*
id   name1      number1     name2      number2     name3   number3 
---- ---------- ----------- ---------- ----------- ---------- ------- 
01   aaa        123         bbb        456         NULL       NULL
02   ccc        456         NULL       NULL        NULL       NULL
03   ddd        567         eee        678         fff        789
*/

drop table T,#

[-狙击手-]

create table ta(ID char(2), [NAME] varchar(6), NUMBER int)
insert ta select '01','AAA',123 
insert ta select '01','BBB',456 
insert ta select '01','dddd',234
insert ta select '02','CCC',456  

go



declare @sql varchar(8000)

set @sql = 'select id'

select @sql = @sql + ',max(case when num = '''+cast(num as varchar)+'''  then name else null end) as [name] '+
',max(case when num = '''+cast(num as varchar)+'''  then NUMBER else null end) as [NUMBER] '
from 
(select distinct (select count(*)+1 from ta where id= a.id and a.name > name) as num
from ta a
) aa


exec(@sql+'
from (select *,(select count(*)+1 from ta where id= a.id and a.name > name) as num
from ta a) aaa
group by id')
drop table ta


/*

id   name   NUMBER      name   NUMBER      name   NUMBER      
---- ------ ----------- ------ ----------- ------ ----------- 
01   AAA    123         BBB    456         dddd   234
02   CCC    456         NULL   NULL        NULL   NULL

*/