SQL之 Stuff和For xml path 将多行放入一行

stuff：

作用

stuff(param1, startIndex, length, param2)
将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

示例

select STUFF('abcdefg',1,0,'1234') --结果为'1234abcdefg'
select STUFF('abcdefg',1,1,'1234') --结果为'1234bcdefg'
select STUFF('abcdefg',2,1,'1234') --结果为'a1234cdefg'
select STUFF('abcdefg',2,2,'1234') --结果为'a1234defg'

一.FOR XML PATH 简单介绍

假设现在有一张兴趣爱好表（hobby）用来存放兴趣爱好，表结构如下：

接下来我们来看应用FOR XML PATH的查询结果语句如下：

SELECT * 
FROM @hobby 
FOR XML PATH

结果：

<row>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</row>
<row>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</row>
<row>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</row>

改变XML行节点的名称：

SELECT * 
FROM @hobby 
FOR XML PATH('MyHobby')

原来的行节点<row> 变成在PATH()中自定义的名称<MyHobby>,结果如下：

<MyHobby>
  <hobbyID>1</hobbyID>
  <hName>爬山</hName>
</MyHobby>
<MyHobby>
  <hobbyID>2</hobbyID>
  <hName>游泳</hName>
</MyHobby>
<MyHobby>
  <hobbyID>3</hobbyID>
  <hName>美食</hName>
</MyHobby>

列节点如何改变呢：

SELECT hobbyID as 'MyCode',hName as 'MyName' 
FROM @hobby 
FOR XML PATH('MyHobby')

列的节点名称也会变成自定义的名称 <MyCode>与<MyName>结果如下：

<MyHobby>
  <MyCode>1</MyCode>
  <MyName>爬山</MyName>
</MyHobby>
<MyHobby>
  <MyCode>2</MyCode>
  <MyName>游泳</MyName>
</MyHobby>
<MyHobby>
  <MyCode>3</MyCode>
  <MyName>美食</MyName>
</MyHobby>

自定义输出方式：

SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('')

结果如下

[ 爬山 ][ 游泳 ][ 美食 ]

其他类型的列，将它们转换成字符串类型：

SELECT '{'+STR(hobbyID)+'}','[ '+hName+' ]' 
FROM @hobby 
FOR XML PATH('')

二.一个应用场景

一张学生表，列分别为（stuID,sName,hobby）

• stuID代表学生编号
• sName代表学生姓名
• hobby列存学生的爱好

表结构如下：

要求是查询学生表，显示所有学生的爱好的结果集，代码如下：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby 
FROM (
  SELECT sName,(SELECT hobby+',' 
                 FROM student
                 WHERE sName=A.sName
                 FOR XML PATH('')) AS StuList
  FROM student A
  GROUP BY sName
) B

结果如下:

• 通过FOR XML PATH 将爱好，显示成格式为：“ 爱好1，爱好2，爱好3，”的格式！
• 外层的语句:LEFT(StuList,LEN(StuList)-1) 就是来去掉逗号