如何将字符串连接在一起？请帮忙

[freegoal]

我的数据表类似如下：

SN City
---------------------------------
LJ832785 beijing
LJ832785 shanghai
LJ832785 wuhan
LJ832796 beijing
LJ832796 xian
LJ832796 hong kong
LJ832796 shenzhen

我希望得到如下结果：
SN CityList
---------------------------------
LJ832785 beijing,shanghai,wuhan
LJ832796 beijing,xian,hong kong,shenzhen


请问SQL如何写才能实现这个？谢谢1

[aniude]

--------------
function List(@sn varchar(30))
returns @varchar(200)
as
begin
declare @list varchar(200)
set @list=''
select @list=@list+city+',' from tb
where SN=@sn
@list=right(@list,len(@list)-1)
end
select distinct SN,CityList = dbo.List(SN) from tb

[aniude]

--------------
function List(@sn varchar(30))
returns @varchar(200)
as
begin
declare @list varchar(200)
set @list=''
select @list=@list+city+',' from tb
where SN=@sn
@list=right(@list,len(@list)-1)
return @list
end
select distinct SN,CityList = dbo.List(SN) from tb

[huailairen]

create function addstr(@v varchar(80))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+','+city
from 表
where sn=@v
order by city
end

select SN , dbo.addstr(SN) as CityList
from 表
group by SN

[huailairen]

select distinct SN , dbo.addstr(SN) as CityList
from 表
group by SN

[dulei115]

if object_id('test') is not null drop table test
select 'LJ832785' as SN, 'beijing' as City
into test
union select 'LJ832785', 'shanghai'
union select 'LJ832785', 'wuhan'
union select 'LJ832796', 'beijing'
union select 'LJ832796', 'xian'
union select 'LJ832796', 'hong kong'
union select 'LJ832796', 'shenzhen'

if object_id('testfun') is not null drop function testfun
go
create function testfun (@SN varchar(8)) returns varchar(200)
as
begin
declare @s varchar(200)
set @s = ''
select @s = @s + City + ',' from test where SN = @SN
return left(@s, len(@s) - 1)
end
go
select distinct SN, dbo.testfun(SN) as City
from test
/*
SN City
LJ832785 beijing,shanghai,wuhan
LJ832796 beijing,hong kong,shenzhen,xian
*/
drop table test
drop function testfun