create table tb
(id int,name varchar(100))
insert into tb
select 1,'aaa'
union select 1,'aac'
union select 1,'a3a'
union select 1,'ada'
union select 2,'aa'
union select 2,'a5aa'
union select 2,'aa5'
union select 2,'aafd'
union select 3,'aaafd'
union select 3,'aaafd'
union select 3,'aafdfd'
我想达到的功能是：
id name
--- -----------------
1 a3a*aaa*aac*ada
2 a5aa*aa*aa5*aafd
3 aaafd*aafdfd
********************* 参考方法 **********************
create function test(@id int) returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s = ''
select @s = @s + [name] + '*'
from tb where id = @id
set @s = left(@s,len(@s) - 1)
return(@s)
end
go
select distinct id, dbo.test(id)
from tb