SQL user defined aggregate order of values preserved?
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:
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.
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 theIsInvariantToOrder
attribute?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.
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.
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/
精彩评论