将查出的多条记录的某个字段拼接成一个字符串(FOR XML PATH)

一.实际应用场景

增加数据表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字符串去除最后一个","的