一对多表 多条数据如何合并成一条

[彩色铅笔绘蓝图]

文章表
article
id name
1 奥巴马踢足球

标签表
tag
id name
1 体育
2 新闻
3 娱乐

关联表
article_tag_rel
article_id tag_id
1 1
1 2
1 3

设想的结果
id name tag_ids tag_names
1 奥巴马踢足球 1,2,3 体育,新闻,娱乐

请教各位大神 这条查询的sql语句该怎么写

[彩色铅笔绘蓝图]

我想在查询文章的时候 查询出与他关联的所有标签 id和name 方便我程序直接返回结果 。 不用在根据文章id去动态查询他的标签

[骑着蜗牛去爬山]

with article(id,name)as(
select 1,'奥巴马踢足球'),
tag(id,name)as(
select 1,'体育' union all
select 2,'新闻' union all
select 3,'娱乐'),
article_tag_rel(article_id,tag_id)as(
select 1,1 union all
select 1,2 union all
select 1,3)
select name,stuff((select ','+rtrim(tag.id)  from article_tag_rel atr join tag on atr.tag_id=tag.id where atr.article_id=article.id for xml path('')),1,1,'') as tag_ids,stuff((select ','+tag.name  from article_tag_rel atr join tag on atr.tag_id=tag.id where atr.article_id=article.id for xml path('')),1,1,'') as tag_names from article

[学海无涯-回头是岸]

FOR XML 就可以了

[二月十六]

--测试数据
if not object_id(N'Tempdb..#article') is null
	drop table #article
Go
Create table #article([id] int,[name] nvarchar(26))
Insert #article
select 1,N'奥巴马踢足球'
GO
if not object_id(N'Tempdb..#tag') is null
	drop table #tag
Go
Create table #tag([id] int,[name] nvarchar(22))
Insert #tag
select 1,N'体育' union all
select 2,N'新闻' union all
select 3,N'娱乐'
GO
if not object_id(N'Tempdb..#article_tag_rel') is null
	drop table #article_tag_rel
Go
Create table #article_tag_rel([article_id] int,[tag_id] int)
Insert #article_tag_rel
select 1,1 union all
select 1,2 union all
select 1,3
Go
--测试数据结束
SELECT  aa.id ,
        aa.name ,
        STUFF(( SELECT  ',' + RTRIM(c.id)
                FROM    #article a
                        JOIN #article_tag_rel b ON a.id = b.article_id
                        JOIN #tag c ON b.tag_id = c.id
                WHERE   a.id = aa.id
                        AND a.name = aa.name
              FOR
                XML PATH('')
              ), 1, 1, '') AS tag_ids,
        STUFF(( SELECT  ',' + c.name
                FROM    #article a
                        JOIN #article_tag_rel b ON a.id = b.article_id
                        JOIN #tag c ON b.tag_id = c.id
                WHERE   a.id = aa.id
                        AND a.name = aa.name
              FOR
                XML PATH('')
              ), 1, 1, '') AS tag_names   
FROM    #article aa
GROUP BY aa.id,aa.name