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
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
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
去掉行吗?