收集表中行加入到另一表的一个字段中解决方法

收集表中行加入到另一表的一个字段中
原有表A
id value
0 a
0 b
0 c
1 b
1 c

希望得到下表：
id value
0 a/b/c
1 b/c

[解决办法]
自定义函数
[解决办法]

SQL code

create  function wsp(@id int)returns varchar(50)asbegin    declare @sql varchar(8000)    select @sql=isnull(@sql+'/','')+value from lo where id=@id    return @sqlend调用函数：select distinct id,dbo.wsp(id) [value] from A
[解决办法]
SQL code
CREATE   TABLE   #Tab   (  id int,value varchar(10))insert   into   #Tabselect  0, 'a' union   all  select   0, 'b' union   all  select   0, 'c' union   all  select   1, 'b' union   all  select   1, 'c'select * into # from #tab order by iddeclare @col1 varchar(100),@col2 varchar(1000)update # set @col2=case when @col1=id then @col2+'/'+value else value end,@col1=id,value=@col2select id,max(value) as value from # group by iddrop table #
[解决办法]
SQL code
你可以参考如下：/*有表tb, 如下：id    txt----- ------1     aa1     bb2     aaa2     bbb2     ccc需要得到结果：id     values------ -----------1      aa,bb2      aaa,bbb,ccc即： group by id, 求 txt 的和（字符串相加）*/create table tb(id int,txt varchar(100))goinsert into tbselect 1,'aaa' union allselect 1,'bbb' union allselect 2,'ccc' union allselect 3,'ddd' union allselect 3,'eee' union allselect 3,'fff'go--写一个聚合函数：create function dbo.fn_Merge(@id int)returns varchar(8000)asbegin   declare @r varchar(8000)   set @r=''   select @r=@r+';'+txt from tb where id=@id   return stuff(@r,1,1,'')endgo-- 调用函数select id, dbo.fn_Merge(id) as txt from tb group by id  godrop table tbdrop function fn_Merge