处理分类拼接字段值的解法

[zhjiang168]

以下是变量的方法解决分类拼接字段值

delcare @s varchar(8000),@i int
create table #t(id int, s varchar(100))
insert #t select 1,'a' union select 1,'b' union 2,'a'

select * into #t0 from #t order by id

set @s=''
set @i=-1

update #t0 set @s=case when @i=id then @s+','+s else s end, @i=id,id=@i,s=@s

select id,max(s) from #t0 grpup by id
drop table #t,#t0
显示的结果是：
id s
1 a,b
2 a

请问用自定义函数方法实现在上面同样的功能应该怎么写？？

[newdongkui]

create function yourfunction ( @id int )
returns varchar(100)
as
begin
declare @_tempstr
set @_tempstr = ''
select @_tempstr = @_tempstr +','+ yourfield from yourtable where id = @id
select @_tempstr = case when @_tempstr <> '' then left( @_tempstr ,len(@_tempstr)-1) else null end
return @_tempstr
end
go
select id , dbo.yourfuction(id) from yourtable

[Andy__Huang]

create table tb(id int, s varchar(100))
insert tb select 1,'a'
union select 1,'b'
union select 2,'a'

select * from tb

--函數
create function dbo.fn_1(@a int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[s]+',' from tb where id=@a
return (left(@s,len(@s)-1))
end

--刪除
drop table tb
drop function dbo.fn_1

--結果
select id,s=dbo.fn_1(id) from tb group by id
id s
---------------
1 a,b
2 a

[funsuzhou]

我的思路和楼上一样

[zhjiang168]

to : hdhai9451(※★開拓者...准備去長安☆※)

select @s=@s+[s]+',' from tb where id=@a 这句中的中括号是什么意思？？
去掉行吗？