SQL Server 中的自定义聚合函数 (concat)

问题描述

问题:我想编写一个自定义聚合函数来连接 group by 上的字符串.

Question: I want to write a custom aggregate function that concatenates string on group by.

这样我就可以做一个

SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2)  as f2
FROM TABLE_XY
GROUP BY FIELD1, FIELD2

我发现的只是 SQL CRL 聚合函数，但我需要 SQL，没有 CLR.​​

All I find is SQL CRL aggregate functions, but I need SQL, without CLR.

1
查询应如下所示:

1
The query should look like this:

   SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2)  as f2
    FROM TABLE_XY
    GROUP BY FIELD0

编辑 2:
确实，没有 CLR 是不可能的.
但是，可以修改 astander 的 subselect 答案，因此它不会对特殊字符进行 XML 编码.

Edit 2:
It is true that it isn't possible without CLR.
However, the subselect answer by astander can be modified so it doesn't XML-encode special characters.

对此的细微变化是在FOR XML PATH"之后添加:,

The subtle change for this is to add this after "FOR XML PATH":,

 TYPE
                  ).value('.[1]', 'nvarchar(MAX)')

这里有几个例子

DECLARE @tT table([A] varchar(200), [B] varchar(200));

INSERT INTO @tT VALUES ('T_A', 'C_A');
INSERT INTO @tT VALUES ('T_A', 'C_B');
INSERT INTO @tT VALUES ('T_B', 'C_A');
INSERT INTO @tT VALUES ('T_C', 'C_A');
INSERT INTO @tT VALUES ('T_C', 'C_B');
INSERT INTO @tT VALUES ('T_C', 'C_C');

SELECT
      A AS [A]
      ,
      (
            STUFF
            (
                    (
                             SELECT DISTINCT
                                   ', ' + tempT.B AS wtf
                             FROM @tT AS tempT
                             WHERE (1=1)
                             --AND tempT.TT_Status = 1
                             AND tempT.A = myT.A
                             ORDER BY wtf
                             FOR XML PATH, TYPE
                    ).value('.[1]', 'nvarchar(MAX)')
                    , 1, 2, ''
            )
      ) AS [B]
FROM @tT AS myT
GROUP BY A





SELECT
      (
            SELECT
                  ',äöü<>' + RM_NR AS [text()]
            FROM T_Room
            WHERE RM_Status = 1
            ORDER BY RM_NR
            FOR XML PATH('')

      ) AS XmlEncodedNoNothing


      ,
      SUBSTRING
      (
            (
                  SELECT
                        ',äöü<>' + RM_NR  AS [data()]
                  FROM T_Room
                  WHERE RM_Status = 1
                  ORDER BY RM_NR
                  FOR XML PATH('')
            )
            ,2
            ,10000
      ) AS XmlEncodedSubstring


      ,
      (
            STUFF
            (
                  (
                        SELECT ',äöü<>' + RM_NR + CHAR(10)
                        FROM T_Room
                        WHERE RM_Status = 1
                        ORDER BY RM_NR
                        FOR XML PATH, TYPE
                  ).value('.[1]', 'nvarchar(MAX)')
                  , 1, 1, ''
            )
      ) AS XmlDecodedStuffInsteadSubstring

推荐答案

您不能在 CLR 之外编写自定义聚合.

You cannot write custom aggregates outside of the CLR.

您可以在纯 T-SQL 中编写的唯一类型的函数是标量和表值函数.

The only type of functions you can write in pure T-SQL are scalar and table valued functions.

比较 CREATE AGGREGATE 的页面，该页面仅列出 CLR 样式选项，带有 CREATE FUNCTION，其中显示了 T-SQL 和 CLR 选项.

Compare the pages for CREATE AGGREGATE, which only lists CLR style options, with CREATE FUNCTION, which shows T-SQL and CLR options.

这篇关于SQL Server 中的自定义聚合函数 (concat)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！