求一句sql或者过程，类似于split函数！上一帖子发错了！
create table tableA
(
Aid varchar(2),
Aname varchar(20)
)
insert into tableA values('A1','姓名1');
insert into tableA values('A2','姓名2');
insert into tableA values('A3','姓名3');
insert into tableA values('A4','姓名4');
insert into tableA values('A5','姓名5');

create table tableB
(
id varchar(2),
Bid varchar(50)
)
insert into TableB values('1','A1,A2,A4');
insert into TableB values('2','A3,A5');
insert into TableB values('3','A1,A5');
insert into TableB values('4','A2,A3');


求一句sql语句，过程，或者函数，实现下面的效果，以tableB为查询对象，得到如下效果

idBidAnames
1A1,A2,A4 姓名1，姓名2，姓名4
2A3,A5姓名3，姓名5
3A1,A5姓名1，姓名5
4A2,A3姓名2，姓,3














[解决办法]

SQL code

参考:分解字符串包含的信息值后然后合并到另外一表的信息(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)/*问题描述tbaID  classid   name1     1,2,3   西服 2     2,3     中山装3     1,3     名裤tbb id   classname1     衣服2     上衣3     裤子我得的结果是id   classname            name1     衣服,上衣,裤子      西服 2     上衣，裤子          中山装3     衣服，裤子          名裤*/-------------------------------------------------------sql server 2000中的写法create table tba(ID int,classid varchar(20),name varchar(10))insert into tba values(1,'1,2,3','西服')insert into tba values(2,'2,3'  ,'中山装')insert into tba values(3,'1,3'  ,'名裤')create table tbb(ID varchar(10), classname varchar(10))insert into tbb values('1','衣服')insert into tbb values('2','上衣')insert into tbb values('3','裤子')go--第1种方法，创建函数来显示create function f_hb(@id varchar(10))returns varchar(1000)asbegin  declare @str varchar(1000)  set @str=''  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0  return stuff(@str,1,1,'')endgo select id,classid=dbo.f_hb(classid),name from tbadrop function f_hb/*id          classid       name       ----------- ------------- ---------- 1           衣服,上衣,裤子 西服2           上衣,裤子      中山装3           衣服,裤子      名裤（所影响的行数为 3 行）*/--第2种方法.updatewhile(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))update tbaset classid= replace(classid,tbb.id,tbb.classname)from tbbwhere charindex(tbb.id,tba.classid)>0select * from tba/*ID          classid              name       ----------- -------------------- ---------- 1           衣服,上衣,裤子       西服2           上衣,裤子            中山装3           衣服,裤子            名裤（所影响的行数为 3 行）*/drop table tba,tbb--------------------------------------sql server 2005中先分解tba中的classid,然后再合并classnamecreate table tba(ID int,classid varchar(20),name varchar(10))insert into tba values(1,'1,2,3','西服')insert into tba values(2,'2,3'  ,'中山装')insert into tba values(3,'1,3'  ,'名裤')create table tbb(ID varchar(10), classname varchar(10))insert into tbb values('1','衣服')insert into tbb values('2','上衣')insert into tbb values('3','裤子')goSELECT id , classname , name FROM(  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from   (    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B  ) tbc , tbb where tbc.classid = tbb.id  ) T)A OUTER APPLY(  SELECT [classname]= STUFF(REPLACE(REPLACE((    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from     (      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B    ) tbc , tbb where tbc.classid = tbb.id  ) N  WHERE id = A.id and name = A.name  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, ''))Norder by iddrop table tba,tbb/*id          classname      name----------- -------------- ----------1           衣服,上衣,裤子 西服2           上衣,裤子      中山装3           衣服,裤子      名裤(3 行受影响)*/