SQL Server 行转列(FOR XML PATH)

;with #t as (select '张三' 姓名, '语文' 科目, 74 成绩 union
select '张三' , '数学' , 83 union
select '张三' , '物理' , 93 union
select '李四' , '语文' , 74 union
select '李四' , '数学' , 84 union
select '李四' , '物理' , 84
)SELECT 姓名,UserList FROM (
SELECT 姓名,
    stuff((SELECT ','+科目+','+cast(成绩 as varchar(10)) FROM #t WHERE 姓名=A.姓名 order by 科目 FOR XML PATH('')),1,1,'') AS UserList
FROM #t A
GROUP BY 姓名
) B