请教SQL语句，将子表的多条记录中的内容合并在一条记录
查询tb_a,tb_b,一个a_id在tb_b中可能会有多条记录，也可能没有。
需要的查询结果如下，请教SQL语句该如何写？
表tb_a
id dep
1 A
2 B
3 C
4 D
表tb_b
id a_id name
1 1 aaa
2 2 bbb
3 2 ccc
4 2 ddd
5 4 eee
希望的结果
a_iddepname
1Aaaa
2Bbbb,ccc,ddd
4Deee
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-31 10:16:33
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tb_a]
if object_id('[tb_a]') is not null drop table [tb_a]
go
create table [tb_a]([id] int,[dep] varchar(1))
insert [tb_a]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'
--> 测试数据：[tb_b]
if object_id('[tb_b]') is not null drop table [tb_b]
go
create table [tb_b]([id] int,[a_id] int,[name] varchar(3))
insert [tb_b]
select 1,1,'aaa' union all
select 2,2,'bbb' union all
select 3,2,'ccc' union all
select 4,2,'ddd' union all
select 5,4,'eee'
--------------开始查询--------------------------
;WITH ym AS (
select a.*,b.NAME from [tb_a] a INNER JOIN [tb_b] b ON a.id=b.a_id)
select a.id,a.dep,
stuff((select ','+name from ym b
where b.id=a.id and b.dep=a.dep
for xml path('')),1,1,'') 'name'
from ym a
group by a.id,a.dep
----------------结果----------------------------
/*
id dep name
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 A aaa
2 B bbb,ccc,ddd
4 D eee
*/
