两表联查，一对多，显示数据以少的为主，多的数据显示到一行

Question

CREATE TABLE #a(a_id int,a_name int)
INSERT INTO #a
select 1, 11 UNION all
select 2, 12CREATE TABLE #b(b_id INT,a_id INT,b_name VARCHAR(50))
INSERT INTO #b
select 1, 1, 'aa' UNION ALL 
select 2, 1, 'bb' UNION ALL 
select 3, 2, 'cc' UNION ALL 
select 4, 2, 'dd'
;WITH cte AS
(
SELECT #a.a_name,#b.b_name FROM #a
LEFT JOIN  #b ON #a.a_id=#b.a_id
)
SELECT  a_name,
(SELECT STUFF((
SELECT ','+b_name FROM cte b WHERE a.a_name=b.a_name FOR XML PATH('')),1,1,''))b_name
FROM cte a
GROUP BY a_namea_name      b_name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11          aa,bb
12          cc,dd(2 行受影响)

Answer 1

CREATE TABLE a(a_id int,a_name int)
INSERT INTO a
select 1,    11 UNION all
select 2,    12
 
CREATE TABLE b(b_id INT,a_id INT,b_name VARCHAR(50))
INSERT INTO b
select 1,    1,    'aa' UNION ALL 
select 2,    1,    'bb' UNION ALL 
select 3,    2,    'cc' UNION ALL 
select 4,    2,    'dd'
goselect distinct 
       a_name ,
       
       STUFF((select ','+b_name from b bb 
              where bb.a_id = a.a_id
              for xml path('')),1,1,'') as b_name
from a
inner join b 
        on a.a_id = b.a_id
/*
a_name b_name
11 aa,bb
12 cc,dd
*/    

Answer 2

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-12 21:19:45
-- 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: )
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([a_id] int,[a_name] int)
insert [a]
select 1,11 union all
select 2,12
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([b_id] int,[a_id] int,[b_name] varchar(2))
insert [B]
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,2,'cc' union all
select 4,2,'dd'
--------------开始查询--------------------------;WITH ym AS 
(select a.a_name, b_name
from [a] INNER JOIN [b] ON a.a_id=b.a_id)
select a.a_name,
stuff((select ','+b_name from ym b 
       where b.a_name=a.a_name
       for xml path('')),1,1,'') 'b_name'
from ym a
group by  a.a_name
----------------结果----------------------------
/* 
a_name      b_name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11          aa,bb
12          cc,dd
*/