★多表合并★把一对多的关联表中的数据合并到一张中以后用字符隔开的问题

现有一张表A是这样的
id name  
----------------
1 tom
2 jack
3 bone

表B

id address
------------------
1 beijing
1 shanghai
1 tianjin
2 beijing
2 chengdu
3 liaoning
3 shanghai
3 nanjing

我想合并后把A变成这样
id name address
-------------------------------------------
1 tom beijing%shanghai%tianjin
2 jack beijing%chengdu
3 bone liaoning%shanghai%nanjing

怎样执行效率能最快呢？数据量很大的话。

SQL code

select 
Name,
address=stuff((select '%'+address from B where ID=a.ID for xml path('')),1,1,'')
from A

SQL code

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#A') is null
    drop table #A
Go
Create table #A([id] int,[name] nvarchar(4))
Insert #A
select 1,N'tom' union all
select 2,N'jack' union all
select 3,N'bone'
Go

 
if not object_id(N'Tempdb..#B') is null
    drop table #B
Go
Create table #B([id] int,[address] nvarchar(8))
Insert #B
select 1,N'beijing' union all
select 1,N'shanghai' union all
select 1,N'tianjin' union all
select 2,N'beijing' union all
select 2,N'chengdu' union all
select 3,N'liaoning' union all
select 3,N'shanghai' union all
select 3,N'nanjing'
Go
select Name, address=stuff((select '%'+address from #B where ID=a.ID for xml path('')),1,1,'') from #A AS a
/*
Name    address
tom    beijing%shanghai%tianjin
jack    beijing%chengdu
bone    liaoning%shanghai%nanjing
*/

SQL2000時需要用函數，參照

SQL code

--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[Col2] nvarchar(1))
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go

合并表:

SQL2000用函数:

go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
    return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab

go