create table tb(id varchar(10),name varchar(10))
insert tb values('1','a')
insert tb values('1','b')
insert tb values('1','c')
insert tb values('2','a')
insert tb values('2','b')
insert tb values('3','c')
insert tb values('3','b')
go
create function f_str(@id varchar(10))
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select @re=@re+','+name from tb where id=@id
return stuff(@re,1,1,'')
end
go
select id,dbo.f_str(id) from tb group by iddrop table tb
drop function f_str
insert tb values('1','a')
insert tb values('1','b')
insert tb values('1','c')
insert tb values('2','a')
insert tb values('2','b')
insert tb values('3','c')
insert tb values('3','b')
go
create function f_str(@id varchar(10))
returns varchar(4000)
as
begin
declare @re varchar(4000)
set @re=''
select @re=@re+','+name from tb where id=@id
return stuff(@re,1,1,'')
end
go
select id,dbo.f_str(id) from tb group by iddrop table tb
drop function f_str
SQL>select id, strsum(name,',') name from n group by id;
+----+-------+
| ID | NAME |
+----+-------+
| 1 | a,b,c |
| 2 | a,b |
| 3 | c,b |
+----+-------+
http://www.hgsql.com