我的数据表类似如下: 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
---------------------------------
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
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+','+city
from 表
where sn=@v
order by city
endselect SN , dbo.addstr(SN) as CityList
from 表
group by SN
from 表
group by SN
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
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
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