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 endselect 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, DATAdeclare @ID int, @DATA varchar(100) update #T set @DATA=case when ID=@ID then @DATA+','+DATA else DATA end, @ID=ID, DATA=@DATAselect ID, DATA=max(DATA) from #T group by ID--result ID DATA ----------- ------------- 1 A,B 2 C,D,E,F(2 row(s) affected)
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
endselect id,dbo.f_test(id) data from test group by id
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, DATAdeclare @ID int, @DATA varchar(100)
update #T set
@DATA=case when ID=@ID then @DATA+','+DATA else DATA end,
@ID=ID,
DATA=@DATAselect ID, DATA=max(DATA)
from #T
group by ID--result
ID DATA
----------- -------------
1 A,B
2 C,D,E,F(2 row(s) affected)