CODEWITHSUNDEEP
sqlsql-serversql-order-byconcatenation
klashar
klashar

Reputation: 2561

Why SQL Server Ignores vaules in string concatenation when ORDER BY clause specified

I have the following table

Created    Comment
2010/10/10 Text1 
2010/11/11 Text2
2010/12/12 Text3

I need gather all comments into the single string

SELECT  @Comment = COALESCE(@Comment, '')
   + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(30), [dbo].[Comment].[Created], 101) + ': ' + ISNULL([Comment].[Text], '')
    FROM Comment

Without ordering it works as expected end return all thee comments. But after running following code where ORDER BY clause is added:

SELECT  @Comment = COALESCE(@Comment, '')
   + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(30), [Created], 101) + ': ' + ISNULL([Text], '')
    FROM Comment
  ORDER BY Created

Return only the last comment. Does any body know why ORDER BY leads to the strange result in concatenation?

PS: It work fine if FOR XML clause used instead of concatenation Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?.

Upvotes: 2

Views: 751

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

Because you are relying on undocumented behaviour.

Microsoft say "The correct behavior for an aggregate concatenation query is undefined.". If the compute scalar moves to the wrong place in the plan then it just stops working!

Upvotes: 6

Related Questions