求拼接表某列拼接合计的sql

表1 id value1 value2 chechi
1 35 42 123
1 12 20 234
2 10 20 123

表2 chechi name
123 张三
234 李四

想得到 id value1_heji value2_heji name
1 47 62 张三,李四
2 10 20 张三

select * from 表1 left join 表2 on 表1.chechi = 表2.chechi

select * from 表1 a inner join 表2 b on a.chechis=b.chechi

利用合并行函数：GROUP_CONCAT()

select 表1.id,
sum(value1) value1_heji,
sum(value2) value2_heji,
GROUP_CONCAT(表2.name SEPARATOR ',') name
from 
表1 left join 表2 on 表1.chechi = 表2.chechi group 表1.id

邀请大神

select t1.id,sum(value1) as value1,sum(value2) as value2,t2.name from Table1 as t1
left join
(
select
A.id
, (
stuff(
(select ',' + B.name from (select
t1.id,t2.name from Table1 as t1
left join Table2 t2 on t2.chechi=t1.chechi) as B where B.id = A.id for xml path('')),
1,
1, '')
) as name
from (select
t1.id,t2.name from Table1 as t1
left join Table2 t2 on t2.chechi=t1.chechi) as A group by A.id
) t2 on t2.id=t1.id
group by t1.id,t2.name
order by t1.id