SQL用户定义的值的聚合顺序是否保留？

Im using the code from this MSDN page to create a user defined aggregate to concatenate strings with group by's in SQL server. One of my requirements is that the order of the concatenated values are the same as in the query. For example:

我使用此MSDN页面中的代码创建用户定义的聚合，以在SQL Server中将字符串与group by连接起来。我的一个要求是连接值的顺序与查询中的顺序相同。例如：

Value   Group
1       1
2       1
3       2
4       2

Using query

使用查询

SELECT
  dbo.Concat(tbl.Value) As Concat,
  tbl.Group
FROM
  (SELECT TOP 1000
     tblTest.*
  FROM 
    tblTest
  ORDER BY 
    tblTest.Value) As tbl
GROUP BY
  tbl.Group

Would result in:

会导致：

Concat  Group
"1,2"   1
"3,4"   2

The result seems to always come out correct and as expected, but than I came across this page that states that the order is not guaranteed and that attribute SqlUserDefinedAggregateAttribute.IsInvariantToOrder is only reserved for future use.

结果似乎总是正确并且正如预期的那样，但是我遇到的这个页面表明订单无法保证，并且该属性SqlUserDefinedAggregateAttribute.IsInvariantToOrder仅保留供将来使用。

So my question is: Is it correct to assume that the concatenated values in the string can end up in any order?
If that is the case then why does the example code on the MSDN page use the IsInvariantToOrder attribute?

所以我的问题是：假设字符串中的连接值可以以任何顺序结束是否正确？如果是这种情况那么为什么MSDN页面上的示例代码使用IsInvariantToOrder属性？

2 个解决方案

#1

I suspect a big problem here is your statement "the same as in the query" - however, your query never defines (and cannot define) an order by the things being aggregated (you can of course order the groups, by having a ORDER BY after the GROUP BY). Beyond that, I can only say that it is based purely on a set (rather than an ordered sequence), and that technically the order is indeed undefined.

我怀疑这里的一个大问题是你的语句“与查询中的相同” - 但是，你的查询永远不会通过聚合的东西来定义（并且不能定义）一个订单（你当然可以通过ORDER BY命令这些组在GROUP BY之后）。除此之外，我只能说它完全基于一个集合（而不是一个有序的序列），从技术上讲，这个顺序确实是未定义的。

#2

While the accepted answer is correct, I wanted to share a workaround that others may find useful. Warning: it involves not using a user-defined aggregate at all :)

虽然接受的答案是正确的，但我想分享其他人可能觉得有用的解决方法。警告：它根本不涉及使用用户定义的聚合:)

The link below describes an elegant way to build a concatenated, delimited list using only a SELECT statement and a varchar variable. The upside (for this thread) is that you can specify the order in which the rows are processed. The downside is that you can't easily concatenate across many different subsets of rows without painful iteration.

下面的链接描述了仅使用SELECT语句和varchar变量构建连接的分隔列表的优雅方法。上行（对于此线程）是您可以指定处理行的顺序。缺点是您无法轻松地跨越许多不同的行子集进行连接而无需进行痛苦的迭代。

Not perfect, but for my use case was a good workaround.

不完美，但对于我的用例是一个很好的解决方法。

http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/

#1

#2