高手请进，请教一个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)
