这样的SQL语句怎么写

[buduanxuexi]

A表
列名1 列名2
1 xxx

B表
列名1 列名2
s1 1
s2 1
s3 1

其中A表的列名1是B表列名2的外键

我现在想显示如下信息
列名1 列名3
1 s1/s2/s3

请问如何写这样的SQL语句

[tre_sdlpq]

A表
列名1 列名2
1 xxx

B表
列名1 列名2
s1 1
s2 1
s3 1

declare @test as varchar(100)
set @test=""
select 列名1,(select @test =@test +'\'from A left join B where A.列名1=B.列名2)
from B

[yrwx001]

create function fn_test(@col1 int)
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
select @str = @str + '/' +列名1 from B where 列名2 = '@col1'
set @str = stuff('',1,1);
return @str
end

go
select 列名1 ,列名3 = dbo.fn_test(名1) from A

[buduanxuexi]

对了，还要去除重复，比如列名1包含S1 S2 S3 S1 则显示只需要显示s1/s2/s3

[yrwx001]

create function fn_test(@col1 int)
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from (select distinct 列名1 from B where 列名2 = '@col1 ' ) G
set @str = stuff(@str,1,1,'');
return @str
end

go
select 列名1 ,列名3 = dbo.fn_test(名1) from A

[-狙击手-]

create   function   fn_test(@col1   int) 
reurn   varchar(100) 
as 
begin 
declare   @str   varchar(100) 
set   @str   =   ' ' 
select   @str   =   @str   +   '/ '   +列名1   from (select distinct 列名1 from b ) bb   where     列名2   =   '@col1 '   
set   @str   =   stuff( ' ',1,1); 
return   @str 
end 

go 
select   列名1   ,列名3   =   dbo.fn_test(名1)   from   A

[fengming2222]

create function test(@col varchar(100))
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from B where 列名2 = '@col'
set @str = right( @str,len(@str)-1)
return @str
end

go
select 列名1 ,'列名3' = dbo.test(名1) from A

[fengming2222]

create function test(@col varchar(100))
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from (select distinct 列名1 from b ) bb where bb.列名2 = '@col '
set @str = right( @str,len(@str)-1)
return @str
end

go
select 列名1 , '列名3 ' = dbo.test(名1) from A

[internetroot]

/*
A表 
列名1   列名2 
1           xxx 

B表 
列名1   列名2 
s1           1 
s2           1 
s3           1 

其中A表的列名1是B表列名2的外键 

我现在想显示如下信息 
列名1   列名3 
1           s1/s2/s3 

请问如何写这样的SQL语句
*/

use pubs
go

create table ta
(
   col1 varchar(2),
   col2 int
)
create table tb
(
   col1 varchar(2),
   col3 int
)
insert into tb values('s1',1)
insert into tb values('s2',1)
insert into tb values('s3',1)

go

if object_id('pubs..f_hb') is not null
   drop function f_hb
go

--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + '/' + col1 from tb where col3= @a 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go

--调用自定义函数得到结果：
select distinct col3 ,dbo.f_hb(col3) as col3 from tb

drop table tb
drop table ta
drop function f_hb

/*
col1	col3
------------------
1	s1/s2/s3

*/

/*
此程序参考http://topic.csdn.net/u/20071115/11/1a130a85-7801-45dc-8ec8-1a5c4ca3dbfc.html
*/

[buduanxuexi]

都是高手，下午结贴，多谢多谢！