最近的日常题中，有一道题是把同类型的数据合并成一行。如果使用SQLServer2017及以上版本，可以直接使用STRING_AGG()函数。但是2016以下的版本没有这个功能，怎么解决呢？今天给大家介绍一下FORXMLPATH。它用于处理低版本数据库中的数据合并。是比较老的功能了，新版本还是可以用的。什么是FORXMLPATHFORXMLPATH就是将查询结果集以XML形式显示出来，在同一行中显示多行结果。我们通过实例向您介绍它的神奇之处。创建测试数据我们创建一个统计学生兴趣爱好的表CREATETABLEStu_Hobby(Stu_NameNVARCHAR(20),--nameAgeINT,--ageHobbyNVARCHAR(20)--hobbies)INSERTINTOStu_HobbyVALUES(N'张三',19,N'踢足球'),(N'张三',19,N'打篮球'),(N'张三',19,N'游泳'),(N'李四',21,N'看电影'),(N'李四',21,N'看书'),(N'王五',22,N'唱歌'),(N'王五',22,N'玩游戏'),(N'马六',19,N'踢足球'),(N'赵琪',20,N'爬山'),(N'赵琪',20,N'跑步')查询列表ofstudenthobbiesDatainStu_Hobby:Howtouse建立好测试数据后，我们开始查询这张表中的数据，使用FORXMLPATH。选择*FROMdbo.Stu_HobbyFORXMLPATH；结果如下：会生成一段XML代码，我们点击这行代码，会弹出一整个XML页面，由于篇幅较长，我们只截取一部分，如下：另外，我们也可以在FORXMLPATH后面写参数，如果后面有参数，则把节点替换成参数名，例如：SELECT*FROMdbo.Stu_HobbyFORXMLPATH(hobby)结果如图下面：它已经成为我们添加的参数。比较贴近我们实际需求的是下面这个功能。我们可以单独输出某个字段的值。比如我们要查看学生爱好表中Hobby列的具体值，可以这样写：SELECTHobby+','FROMdbo.Stu_HobbyFORXMLPATH('')注意：上面的+是字段拼接，就是把两个字符串用+连接成一个字符串。然后我们删除XML。结果如下：可以看到把我们写的所有兴趣爱好都列出来了，而且没有去重，可以理解为显示该列的所有值。具体实际应用现在我们要将上面学生表中每个学生的兴趣爱好单独显示一行，用“,”隔开。SELECTA.Stu_Name,A.Age,(SELECTHobby+','FROM[dbo].Stu_HobbyWHERE--必须加上匹配条件Stu_Name=A.Stu_NameANDAge=A.AgeFORXMLPATH(''))ASHobbyFROM[dbo].Stu_HobbyAGROUPBYA.Stu_Name,A.Age见证奇迹的时刻到了！！！与我们之前建的表相比，Hobby列的数据已经改为每个学生一行。上面的WHERE条件是必须的，去掉会怎么样？让我们注释掉WHERE条件，看看会发生什么？SELECTA.Stu_Name,A.Age,(SELECTHobby+','FROM[dbo].Stu_Hobby--WHERE--必须加上匹配条件--Stu_Name=A.Stu_NameANDAge=A.AgeFORXMLPATH(''))ASHobbyFROM[dbo].Stu_HobbyAGROUPBYA.Stu_Name,A.Age结果如下：会显示Hobby栏的所有值。显然这不是我们想要的结果。代码优化。不知道小伙伴们有没有找到Hobby栏目。结果末尾多了一个“,”。看起来很尴尬。有什么办法可以去除吗？答案是肯定的。首先使用LEFT()和LEN()函数来处理Hobby列SELECTT.Stu_Name,T.Age,LEFT(T??.Hobby,LEN(T.Hobby)-1)ASHobbyFROM(SELECTA.Stu_Name,A.Age,(SELECTHobby+','FROM[dbo].Stu_HobbyWHERE--必须加上匹配条件Stu_Name=A.Stu_NameANDAge=A.AgeFORXMLPATH(''))ASHobbyFROM[dbo].Stu_HobbyAGROUPBYA.Stu_Name,A.Age)结果如下：这样就完美解决了我们的需求，但是这段代码有点长，能不能短点？答案是肯定的！在简化代码之前，需要先介绍一个与大家配合使用的函数：STUFF()STUFF()函数STUFF()函数的作用STUFF()函数用于删除指定长度的字符，并且可以插入另一组在指定的起点字符处。在STUFF()函数中，如果起始位置或长度值为负数，或者起始位置大于第一个字符串的长度，则返回空字符串。如果要删除的长度大于第一个字符串的长度，则最多删除第一个字符串中的第一个字符。STUFF()函数语法STUFF(character_expression,start,length,character_expression)参数说明character_expression：字符数据表达式。character_expression可以是常量、变量或一列字符或二进制数据。start：一个整数值，指定删除和插入的起始位置。如果start或length为负数，则返回空字符串。如果start比第一个character_expression长，则返回空字符串。start可以是bigint类型。length：一个整数，指定要删除的字符数。如果length比第一个character_expression长，则删除直到最后一个character_expression中的最后一个字符。length可以是bigint类型。返回类型如果character_expression是受支持的字符数据类型，则返回字符数据。如果character_expression是受支持的二进制数据类型，则返回二进制数据。注意事项1.如果起始位置或长度值为负数，或者起始位置大于第一个字符串的长度，则返回空字符串。如果要删除的长度大于第一个字符串的长度，则最多删除第一个字符串中的第一个字符。2.如果结果值大于返回类型支持的最大值，将引发错误。--以上信息来自微软官方文档。定义晕了。让我们来看看如何使用它。示例：SELECTSTUFF('abcdefg',1,0,'1234')--Theresultis'1234abcdefg'SELECTSTUFF('abcdefg',1,1,'1234')--Theresultis'1234bcdefg'SELECTSTUFF('abcdefg',2,1,'1234')--Theresultis'a1234cdefg'SELECTSTUFF('abcdefg',2,2,'1234')--Theresultis'a1234defg'。说了这么多，来看看STUFF是如何解决我们上面的问题的。上面代码：SELECTA.Stu_Name,A.Age,STUFF((SELECT','+HobbyFROM[dbo].Stu_HobbyWHERE--必须加上匹配条件Stu_Name=A.Stu_NameANDAge=A.AgeFORXMLPATH('')),1,1,'')ASHobbyFROM[dbo].Stu_HobbyAGROUPBYA.Stu_Name,A.Age比LEFT短吗？让我们看看结果是否是我们想要的。完美的！好了，FORXMLPATH的介绍到此结束。小伙伴们可以对比一下上面两种优化方式，比较一下哪种方式更容易理解。
