SQL Server 中利用STUFF和FOR XML PATH()实现多行数据的拼接

将某一类下的信息显示成一条，如：

 

表tb_Student中数据：

   ID            Class_ID           StudentName

--------------------------------------------------------------

  1                   1                          张三 

  2                   1                          李四

  3                   1                          王五

  4                   2                          马六

  5                   2                          赵七

  6                   3                          孙八             

需求：

以Class_ID为单位分组显示数据如下：

    Class_ID                        StudentsName

------------------------------------------------------------------------

         1                             张三，李四，王五

         2                             马六，赵七

         3                              孙八

SQL语句：

<span style="font-size:12px;">select Class_ID,StndentsName=stuff((select ','+StudentName from tb_Student tmp  where tmp.Class_ID=tb_Student.Class_ID for xml path('')),1,1,'')from tb_Studentgroup by tb_Student.Class_ID</span>

 

这里使用了SQL Server 2005版本以后加入的STUFF()以及FOR XML PATH().

 

1、STUFF()：

①、作用
    stuff(param1, startIndex, length, param2)

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

②、参数
    param1

    一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。

    startIndex

   一个整数值，指定删除和插入的开始位置。如果startIndex或 length 为负，则返回空字符串。如果startIndex比param1长，则返回空字符串。

    startIndex可以是 bigint 类型。

    length 

    一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1中的最后一个字符。length

    可以是 bigint 类型。

③、返回类型
    如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回

     二进制数据。
④、备注
    如果结果值大于返回类型支持的最大值，则产生错误

⑤、示例

<span style="font-size:12px;">select stuff('qwertyuio',1,2,'123')  --结果：123ertyuio     select stuff('qwertyuio',1,4,'123')  --结果：123tyuio      select stuff('qwertyuio',2,6,'123')  --结果：q123io      select stuff('qwertyuio',3,4,'123')  --结果：qw123uio       </span>

 

2、FOR XML PATH()  FOR XML PATH()就是将查询结果集以XML形式展现.，以上面的tb_Student表为数据源，用实例进行解释。

示例1：

SQL语句：

<span style="font-size:12px;">select top 4 Class_ID,StudentName from tb_Student FOR XML PATH()</span>

结果：

<span style="font-size:12px;"><row>     <Class_ID>1</Class_ID>      <StudentName>张三</StudentName>  </row>  <row>      <Class_ID>1</Class_ID>      <StudentName>李四</StudentName>  </row>  <row>     <Class_ID>1</Class_ID>      <StudentName>王五</StudentName>  </row>  <row>    <Class_ID>2</Class_ID>    <StudentName>马六</StudentName>  </row>  </span>

 

示例2（改变XML行节点的名称）：

SQL语句：

<span style="font-size:12px;">select top 4 Class_ID,StudentName from tb_Student FOR XML PATH('Sourse')</span>

结果：

<span style="font-size:12px;"><Sourse>     <Class_ID>1</Class_ID>      <StudentName>张三</StudentName>  </Sourse>  <Sourse>      <Class_ID>1</Class_ID>      <StudentName>李四</StudentName>  </Sourse>  <Sourse>     <Class_ID>1</Class_ID>      <StudentName>王五</StudentName>  </Sourse>  <Sourse>    <Class_ID>2</Class_ID>    <StudentName>马六</StudentName>  </Sourse>  </span>

注：对于改变另外两个节点的名称，只需给字段起别名即可，这里不再赘述。

 

示例3（自定义输出方式）：

SQL语句：

<span style="font-size:12px;">select top 4 '['+Class_ID+'--'+StudentName+']' from tb_Student FOR XML PATH()</span>

结果：

<span style="font-size:12px;">[1--张三][1--李四][1--王五][2--马六]</span>

 

     通过上面的示例及讲解，想必已经明白了开头所用SQL的意思。