求查询语句。

[todown]

A表
id(int) bid(nvarchar(max))
1 1,2,4
2 2,3

B表
id(int) name(nvarchar(max))
1 t1
2 t2
3 t3
4 t4

查询A表的时候，将A表的bid显示的内容替换成B表对应ID的Name内容，查询语句应该怎么写？

查询结果
1 t1,t2,t4
2 t2,t3

求教，谢谢。

[jwdream2008]

首先行专列，接着列转行!http://topic.csdn.net/u/20090522/16/11df6ef4-3338-44a6-9156-64d4b26527ec.html

[SQLCenter]

--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(id int, bid varchar(8))
insert into #a
select 1, '1,2,4' union all
select 2, '2,3'
--> 测试数据：#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(id int, name varchar(8))
insert into #b
select 1, 't1' union all
select 2, 't2' union all
select 3, 't3' union all
select 4, 't4'

select *, bname=stuff((select ','+name from #b where charindex(','+ltrim(id)+',',','+a.bid+',')>0 for xml path('')),1,1,'') from #a as a
/*
id          bid      bname
----------- -------- --------
1           1,2,4    t1,t2,t4
2           2,3      t2,t3
*/

[jwdream2008]

[Quote=引用 2 楼 sqlcenter 的回复:]
SQL code
--> 测试数据：#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(id int, bid varchar(8))
insert into #a
select 1, '1,2,4' union all
select 2, '2,3'
--> 测试数据：#b
if o……
[/Quote]

[todown]

好了，谢谢。