高手请进，请教一个sql
如我现在的表结构Test为  
ID   DATA  
1   A  
1   B  
2   C  
2   D  
2   E  
2   F  

希望将其转换Test2为  
ID   DATA  
1   A,B  
2   C,D,E,F

------解决方案--------------------
create table test(id int,data varchar(10))
insert into test
select 1, 'A '
union all select 1, 'B '
union all select 2, 'C '
union all select 2, 'D '
union all select 2, 'E '
union all select 2, 'F '

create function f_test(@id int)
returns varchar(20)
as
begin
declare @s varchar(20)
select @s= ' '
select @s=@s+data+ ', ' from test where id=@id
select @s=left(@s,len(@s)-1)
return @s
end

select id,dbo.f_test(id) data from test group by id
------解决方案--------------------
create table Test(ID int, DATA char(1))
insert Test select 1, 'A '
union all select 1, 'B '
union all select 2, 'C '
union all select 2, 'D '
union all select 2, 'E '
union all select 2, 'F '

select ID, DATA=cast(DATA as varchar(100)) into #T
from Test
order by ID, DATA

declare @ID int, @DATA varchar(100)
update #T set
@DATA=case when ID=@ID then @DATA+ ', '+DATA else DATA end,
@ID=ID,
DATA=@DATA

select ID, DATA=max(DATA)
from #T
group by ID

--result
ID DATA
----------- -------------
1 A,B
2 C,D,E,F

(2 row(s) affected)