sql for xml path 处理

1.将下列结果集做成 aa 语文,数学

　　　　　　　　　　bb 英语,语文

　这种格式

使用 for xml path 记得去重复

WITH cte AS(SELECT stu.studentname,course.coursename FROM t_student_courseinfo stu_course
    LEFT JOIN t_student stu
    ON stu.studentId=stu_course.studentId
    LEFT JOIN t_course course
    ON course.courseId=stu_course.courseId 
    )

    SELECT * FROM cte

SELECT  studentname,STUFF((SELECT  ';'+coursename FROM cte WHERE studentname=b.studentname GROUP BY coursename FOR XML PATH('')),1,1,'') AS coursename FROM cte  b
GROUP BY studentname

sql for xml path