一.实际应用场景
增加数据表student,列(Id,StudentName,Leason)Id:学号,StudentName:学生姓名,Leason:选修科目
SELECT b.StudentName,LEFT(leasonList,LEN(leasonList)-1) as leasonLists FROM (SELECT StudentName, ( SELECT Leason + ',' FROM student WHERE StudentName = a.StudentName FOR XML PATH ('') ) AS leasonList FROM student a GROUP BY StudentName )AS b
结果:
二.代码分析(FOR XML PATH)
SELECT * FROM student FOR XML PATH ('')
FOR XML PATH 可以将查询结果根据行输出成XML各式,结果:
SELECT Id,'['+StudentName+']'as names,'《'+Leason+'》'as leasons FROM student FOR XML PATH ('')
重新构建自己想要的输出格式:
SELECT StudentName, ( SELECT Leason + ',' FROM student WHERE StudentName = a.StudentName FOR XML PATH ('') ) AS leasonList FROM student a GROUP BY StudentName
根据学生姓名分组,选修课程后边拼接","由此可以发现最后一个拼接结果后边也有一个",":
LEFT(leasons,LEN(leasons)-1) as leasons就是用来截取leasons字符串去除最后一个","的