--插入一下临时数据源

with m as(
select '张三' name,'语文'course,'89'score union all
select '张三' name,'数学'course,'100'score union all
select '张三' name,'英语'course,'40'score union all
select '张三' name,'物理'course,'93'score union all
select '张三' name,'地理'course,'95'score union all
select '张三' name,'化学'course,'80'score union all
select '李四' name,'语文'course,'89'score union all
select '李四' name,'数学'course,'100'score union all
select '李四' name,'英语'course,'40'score union all
select '李四' name,'物理'course,'93'score union all
select '李四' name,'地理'course,'95'score union all
select '李四' name,'化学'course,'80'score
)select * into #info from m

select * from #info

--STUFF 拼接

select distinct name,
STUFF((
SELECT '|' + course+'='+score+'分'
FROM #info B
WHERE B.name=A.name FOR XML PATH('')), 1, 1, '') '拼接串'
from #info A

出处：

https://www.cnblogs.com/xygsj/p/15169398.html