一直没有解决一个SQL语句的问题，在线等，急啊

[sunzhong2003]

SQLSERVER数据库表2个
表1
Id | 会员编号
1 | 2,32
2 | 4,3
表2
会员编号 | 姓名
2 | 小李
3 | 小周
4 | 小吴
32 | 小王

请问如何用SQL语句取出如下格式的
Id | 姓名
1 |小李，小王
2 |小吴，小周

[wgsasd311]

--如果
表1(Id | 会员编号)中的"会员编号"是固定的格式(编号1,编号2)
可以这样写SQL语句:
select a.id,
姓名=(select 姓名 from 表2 where 会员编号=left(a.会员编号,charindex(',',a.会员编号)-1))
+','+(select 姓名 from 表2 where 会员编号=right(a.会员编号,len(a.会员编号)-charindex(',',a.会员编号)))
from 表1 a

[hsj20041004]

可以自己写个查询函数.

[肥胖的柠檬]

create table a1 (a int,b varchar(20))
insert into a1
select 1,'2,32' union all
select 2,'4,3'

create table a2 (a int,c varchar(20))
insert into a2
select 2,'小李' union all
select 3,'小周'union all
select 4,'小吴'union all
select 32,'小王'


--select * from a1

--select * from a2


create function fun_test(@cid varchar(2000))
returns varchar(2000)
as
begin
declare @chr varchar(2000)
set @chr=''
select @chr=@chr+c+',' from a2 where PATINDEX (replace('%,'+str(a)+',%',' ',''),','+@cid+',')>0
return @chr
end

select *,dbo.fun_test(b) from a1

[ojuju10]

create table tb1 (id int,hybh varchar(20))
insert into tb1 select 1,'2,32'
insert into tb1 select 2,'4,3'
create table tb2(hybh varchar(20),name varchar(20))
insert into tb2 select 2,'小李'
insert into tb2 select 3,'小周'
insert into tb2 select 4,'小吴'
insert into tb2 select 32,'小王'



create function abab (@aidlist varchar(20))
returns varchar(20)
as
begin
declare @v varchar(20)
set @v=''
while charindex(',',@aidlist)>0
begin
select @v=@v+','+name from tb2 where hybh=left(@aidlist,charindex(',',@aidlist)-1)
set @aidlist=stuff(@aidlist,1,charindex(',',@aidlist),'')
end
select @v=@v+','+name from tb2 where hybh=@aidlist
set @v=right(@v,len(@v)-1)
return @v
end

select id,dbo.abab(hybh) as hybh from tb1

drop table tb1,tb2
drop function abab

id hybh
----------- --------------------
1 小李,小王
2 小吴,小周

（所影响的行数为 2 行）

[hellowork]

if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
if object_id('fnTest') is not null
drop function fnTest
GO
----创建测试数据
create table tbTest1(Id int,会员编号 varchar(100))
create table tbTest2(会员编号 int,姓名 varchar(20))
insert tbTest1
select 1,'2,32' union all
select 2,'4,3'
insert tbTest2
select 2,'小李' union all
select 3,'小周' union all
select 4,'小吴' union all
select 32,'小王'
GO

[hellowork]

----创建函数,参数为表1的会员编号
create function fnTest(@code varchar(100))
returns varchar(1000)
as
begin
declare @str varchar(1000) /*返回的字符串*/
set @str = ''
set @code = ',' + @code + ',' /*对参数进行处理*/
SELECT @str = @str + ',' + 姓名 FROM
(select top 100 percent 姓名 from tbTest2
where charindex(',' + rtrim(会员编号) + ',',@code) > 0
--注意排序
order by charindex(',' + rtrim(会员编号) + ',',@code)) AS t
return stuff(@str,1,1,'')
end
GO
----查询
select id,dbo.fnTest(会员编号) as 会员编号 from tbTest1
----清除测试环境
drop table tbTest1,tbTest2
drop function fnTest

/*结果
id 会员编号
-------------------------------------------
1 小李,小王
2 小吴,小周
*/