--插入一下临时数据源

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

SqlServer使用STUFF拼接内容_学习

--STUFF 拼接

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

SqlServer使用STUFF拼接内容_数据源_02