在2017年之前的SQL Server中使用String_agg

15 浏览
0 Comments

在2017年之前的SQL Server中使用String_agg

有没有人能帮我让这个查询适用于SQL Server 2014？\n这个查询在Postgresql上运行正常，可能在SQL Server 2017上也可以。在Oracle上，使用的是listagg而不是string_agg。\n以下是SQL代码：\n

select 
    string_agg(t.id,',') AS id
from 
    Table t

\n我在网站上查了一些关于使用XML选项的内容，但我无法理解。

0

2 答案

0 Comments

在SQL Server 2017之前，没有内置的函数可用于将多个行合并为一个字符串。这就是为什么使用FOR XML PATH时，一些特殊字符的值会被转义的原因。例如，下面的代码使用FOR XML PATH进行字符串合并：

SELECT STUFF((SELECT ',' + V.String
              FROM (VALUES('7 > 5'),('Salt & pepper'),('2
lines'))V(String)
              FOR XML PATH('')),1,1,'');

这将返回以下字符串：

7 > 5,Salt & pepper,2
lines'

这显然不是我们想要的结果。为了解决这个问题，可以使用TYPE和value函数来获取XML的值，如下所示：

SELECT STUFF((SELECT ',' + V.String
              FROM (VALUES('7 > 5'),('Salt & pepper'),('2
lines'))V(String)
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'');

这将返回以下字符串：

7 > 5,Salt & pepper,2
lines

这将复制以下行为：

SELECT STRING_AGG(V.String,',')
FROM VALUES('7 > 5'),('Salt & pepper'),('2
lines'))V(String);

当我们需要对数据进行分组时，上述方法并不能满足需求。为了实现这个功能，我们需要使用相关子查询。以下是一个示例数据：

CREATE TABLE dbo.MyTable (ID int IDENTITY(1,1),
                          GroupID int,
                          SomeCharacter char(1));
INSERT INTO dbo.MyTable (GroupID, SomeCharacter)
VALUES (1,'A'), (1,'B'), (1,'D'),
       (2,'C'), (2,NULL), (2,'Z');

我们需要得到以下结果：

| GroupID | Characters |

|---------|------------|

| 1 | A,B,D |

| 2 | C,Z |

为了实现这个目标，我们需要使用以下代码：

SELECT MT.GroupID,
       STUFF((SELECT ',' + sq.SomeCharacter 
              FROM dbo.MyTable sq
              WHERE sq.GroupID = MT.GroupID
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')
FROM dbo.MyTable MT
GROUP BY MT.GroupID;

最后，让我们在这个查询中添加一个ORDER BY子句。例如，假设您想按字符串合并中ID的值降序排序数据：

SELECT MT.GroupID,
       STUFF((SELECT ',' + sq.SomeCharacter 
              FROM dbo.MyTable sq
              WHERE sq.GroupID = MT.GroupID
              ORDER BY sq.ID DESC
              FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(MAX)'),1,1,'')
FROM dbo.MyTable MT
GROUP BY MT.GroupID;

这将产生以下结果：

| GroupID | Characters |

|---------|------------|

| 1 | D,B,A |

| 2 | Z,C |

这种方法的效率不如STRING_AGG函数高，因为它需要多次引用表格。如果在单个查询中执行多个字符串合并，而性能确实是一个问题，那么建议重新考虑是否需要合并，或者考虑升级数据库系统。

0
0 Comments

在SQL Server 2017之前的版本中，可以使用以下方法来实现字符串拼接：

select stuff( (select ',' + cast(t.id as varchar(max))
               from tabel t
               for xml path ('')
              ), 1, 1, ''
            );

`stuff()`函数的唯一目的是移除初始的逗号，真正的工作是由`for xml path`完成的。

然而，如果文本中包含特殊字符如`<`、`>`等，上述方法将无法得到预期的结果。

为了解决XML实体引用的问题，可以使用如下方法：

select stuff( (select ',' + cast(t.id as varchar(max))
               from tabel t
               for xml path (''), TYPE
              ).value('.', 'varchar(MAX)'), 1, 1, ''
            );

需要注意的是，上述方法假设`id`是数值类型，因此需要进行显式的类型转换。

对于模拟"Within Group"功能有没有方法呢？可以参考下面的答案来了解如何实现。

如果将上述方法封装为用户定义函数（UDF）以模拟`STRING_AGG`函数，参数的类型应该是什么？实际上，用户定义函数不能作为聚合函数使用，需要使用CLR（Common Language Runtime）来实现。

关于`FOR XML PATH`的详细解释，可以参考这篇文章。

以上是关于在SQL Server 2017之前使用`STRING_AGG`函数的方法和对应的解决方案的讨论。

0