求赐我一条SQL语句，两表数据拼接SQL语句怎么写？
A表有数据
a,a1,a2
b,b1,b2
c,c1,c2
d,d1,d2
4行
B表有数据
a ,1
a ,2
a ,3
b ,2
b ,4
b ,5
c ,1
c ,2
c ,3
d ,2
d ,4
d ,5
12行
怎么写条SQL语句，才能拼接成为以下四行数据？
a,a1,a2,1,2,3
b,b1,b2,2,4,5
c,c1,c2,1,2,3
d,d1,d2,2,4,5
两表数据拼接SQL语句怎么写 sql
[解决办法]
Create table A (Id1 varchar(10),Id2 varchar(10))
GO
Create table B (Id1 varchar(10),Id int)
Go
Insert Into A
Select 'A','A1' union all
Select 'B','B1' union all
Select 'C','C1' union all
Select 'D','D1' union all
Select 'E','E1'
Insert into B
Select 'A',1 union all
Select 'A',2 union all
Select 'A',3 union all
Select 'A',4 union all
Select 'B',1 union all
Select 'C',1 union all
Select 'C',2 union all
Select 'C',3 union all
Select 'D',1 union all
Select 'E',1 union all
Select 'E',2
Go
Create function f_temp(@Id1 varchar(10))
Returns varchar(100)
as
Begin
Declare @Rtn varchar(100)
Set
@Rtn = ''
Select
@Rtn = @Rtn +cast(Id as varchar(10))+ ','
From
B
Where Id1 = @Id1
RETURN
@Rtn
End
Go
Select *,dbo.f_temp(A.Id1)
From A
[解决办法]
select *,
stuff((select ','+convert(varchar,col2) from b where a.col1=b.col1 for xml path('')),1,1,'')
from a
