在SQLServer上，把从表某字段拼成一个值

--> 测试数据：[father]
if object_id('[father]') is not null 
drop table [father]
go 
create table [father]
(
    [id] int,
    [name] varchar(6)
)
insert [father]
select 1,'王爸爸' union all
select 2,'张爸爸'
--> 测试数据：[son]
if object_id('[son]') is not null 
drop table [son]
go 
create table [son]
(
    [name] varchar(6),
    [father_id] int
)
insert [son]
select '大王子',1 union all
select '二王子',1 union all
select '三王子',1 union all
select '大张子',2 union all
select '二张子',2
;with t
as(
select 
    f.name,
    s.name as Son_name
from 
    [father] f
inner join
    [son] s
on 
    f.id=s.father_id
)
select
    distinct name,
    Son_name=stuff((select ','+Son_name
                    from t b 
                    where b.name=t.name for xml path('')), 1, 1, '')
from t
/*
 
(2 行受影响)
 
(5 行受影响)
name   Son_name
------ --------------------------
王爸爸    大王子,二王子,三王子
张爸爸    大张子,二张子
 
(2 行受影响)
 
 
*/