按照某一字段的相同值合并所对应的行的值

比如如下表test,根据相同的id，合并所有Name列中对应的值，这里指单纯的字符串合并。

一、           字符串合并

表名：test

字段：

         id               int

         name        nvarchar(50)

字段值：

         

期望结果：

         id               nameStr

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

         1                a,b,c

         2                d,e

         3                f

SQL实现：

1、 利用函数解决

 -- 创建处理函数

CREATE FUNCTION dbo.f_str(@id int)

RETURNS varchar(8000)

AS

BEGIN

    DECLARE @r varchar(8000)

    SET @r = ''

    SELECT @r = @r + ',' + [name]

    FROM [test]

    WHERE [id]=@id

    RETURN STUFF(@r, 1, 1, '')

END

GO

 -- 调用函数

SELECT [id], [nameStr]=dbo.f_str(id)

FROM [test]

GROUP BY [id]

 

运行结果：

  

2、 直接用SQL解决

 SELECT *

FROM(

    SELECT DISTINCT [id]

    FROM [test]

)A

OUTER APPLY(

    SELECT

       [nameStr]= STUFF(REPLACE(REPLACE(

       (

           SELECT [name] FROM [test] N

           WHERE [id] = A.id

           FOR XML AUTO

       ), '<N name="', ','), '"/>', ''), 1, 1, '')

)M

 运行结果：

  

 看不懂这个SQL不要紧，下面有详细分析

二、           详细分析

1、  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是一个受支持的 binary 数据类型，则返回二

 进制数据。

注释：

   如果开始位置或长度值是负数，或者如果开始位置大于第一个字符串的长

 度，将返回空字符串。如果要删除的长度大于第一个字符串的长度，将删

 除到第一个字符串中的最后一个字符。如果结果值大于返回类型支持的最

 大值，则产生错误。

示例：

              SELECT STUFF('abcdef', 2, 3, 'ijklmn');

GO

结果：

         

 

SELECT STUFF('abcdef', -2, 3, 'ijklmn');

GO

结果：

         

 

SELECT STUFF('abcdef', 2, 10, 'ijklmn');

GO

结果：

         

 

2、  CROSS APPLY 与 OUTER APPLY（***也可以理解为合并列）

 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。

通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。

APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

 APPLY 有两种形式：CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。

OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为NULL。

 用示例来区分两者：

第一张表：

            表名：student

            字段名：

                     

            字段值：

                     

第二张表：

            表名：student_class

            字段名：

                     

            字段值：

                     

CROSS APPLY

SELECT *

FROM [student] a

CROSS APPLY(

  SELECT [class], [score]

  FROM [student_class]

  WHERE student_id = a.id

) b

 

运行结果：

  

 OUTER APPLY

SELECT *

FROM [student] a

OUTER APPLY(

  SELECT [class], [score]

  FROM [student_class]

  WHERE student_id = a.id

) b

 运行结果：

     

 3、  FOR XML AUTO

将结果转换为XML结构，存在结果字段中，注意时select列中的所有值，例如对于上面2中的student表，执行以下语句

SELECT *

FROM [student]

FOR XML AUTO

 运行结果：

  

转载于:https://www.cnblogs.com/touhoujerry/p/4809490.html