0

I am trying to understand what happens here (one of the answers to this question: Concatenate many rows into a single text string?):

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

So I have an exemplary table 'test' with data:

Col1
----
abc
xyz

Now I execute:

DECLARE @Concatenation VARCHAR(8000) 
SELECT @Concatenation = COALESCE(@Concatenation + ', ', '') + Col1 FROM test
Select @Concatenation

And as a result I have a single result abc, xyz

If I execute:

DECLARE @Concatenation VARCHAR(8000) 
SELECT @Concatenation = Col1 FROM test
Select @Concatenation

I get a single result xyz

The question is why doing an assignment like select @variable = @variable + column causes the @variable to iterate over the whole column? And if I do it like select @variable = column which record from the column is returned? Random?

Community
  • 1
  • 1
zaptask
  • 687
  • 1
  • 8
  • 18
  • that is undocumented hack an yes it works but you should use xml trick to do this. – Giorgi Nakeuri May 17 '16 at 13:42
  • 1
    For your last question: the assignment will use the **last** record. I guess it iterates through all records anyway, and with the `coalesce` method, it will actually store all records. – HoneyBadger May 17 '16 at 13:43
  • @HoneyBadger how do you define last record? There's no clustered or any index whatsoever. – zaptask May 17 '16 at 13:45
  • @zaptask That depends on the optimizer of `sql server`, if you dont tell it with an `order by` – HoneyBadger May 17 '16 at 13:46

1 Answers1

0

This is what you should do instead:

SELECT  STUFF(( SELECT  ',' + name
                FROM    People
              FOR
                XML PATH('')
              ), 1, 1, '')

The first example in your question is a hack and works just by accident. See these threads:

https://connect.microsoft.com/SQLServer/Feedback/Details/383641

nvarchar concatenation / index / nvarchar(max) inexplicable behavior

The second one may return you any value because you are not specifying order.

Community
  • 1
  • 1
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • What do you mean it works by accident? Is there a more verbose description of the problem? Is it SQL Server specific? T-SQL sepecific? Does it work in other dialects? – zaptask May 17 '16 at 13:51
  • Correct me if I'm wrong, but from what I've read, this is a "hack that works by accident" as well. There is no function in `sql server` like `group_concat` of `mysql`. – HoneyBadger May 17 '16 at 13:54
  • @zaptask, I mean this is undocumented feature and can be just removed from future releases of `SQL Server`. And there is some trouble with that. I remember I have read about it. I will search for that. – Giorgi Nakeuri May 17 '16 at 13:56
  • Here is the link on discussion of this hack https://connect.microsoft.com/SQLServer/Feedback/Details/383641 – Giorgi Nakeuri May 17 '16 at 14:01