How variables concatenating rows into strings in sql server ?

Question

Can someone please clarify how variables are executing here,
query1 how all rows are concatenated with comma, there is no while loop there ?
query2 i assign empty strings directly to query but it showing different results
can some explain these iam very much new to sql server. Iam getting so much of confuse how variables are evaluating

select name from names
o/p:
name(col)
s
r
i
n
u
query 1.
declare @VAR varchar(20)
set @VAR =''
select @VAR =@VAR +name+',' from names
select @VAR
(output)
s,r,i,n,u,

query 2.
declare @VAR varchar(20)
select @VAR =''+name+',' from names
select @VAR
output:
u,

Answer 1

Hi，@srini_ksr

Welcome to Microsoft T-SQL Q&A Forum!

There are many ways to do it, try the following two options：

CREATE TABLE names (  
    name char(5)  
  )  
-- insert some values  
INSERT INTO names VALUES ( 's');  
INSERT INTO names  VALUES ( 'r');  
INSERT INTO names  VALUES ( 'i');  
INSERT INTO names  VALUES ( 'u');  
INSERT INTO names  VALUES ( 'n');  
  
select * from names  
  
SELECT STUFF((SELECT ',' + name  
            FROM names  
            FOR XML PATH('')) ,1,1,'') AS name  
  
SELECT left(C, len(C) - 1) as name  
FROM  
(  
SELECT name + ','FROM names  
FOR XML PATH('')  
) AS D(C)  

Ouput：

Bert Zhou

Answer 2

The short answer is don't do that. That method of concatenating strings was always problematic. And today, if you are on SQL 2017 or later, just use the STRING_AGG function. For example

Select String_Agg(name, ',') From names  

Note that that does not put a final comma after the last item, that is you will get "s,r,i,n,u" which in most cases is the desired result. If for some reason you want the final comma after the last item, just do

Select String_Agg(name, ',')  + ',' From names  

Tom

Answer 3

To be true, I don't understand a single word of your post; may can you clarify it more detailed, please?

Answer 4

See:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver16

Query1 is example C.

Query 2 should error with "More than 1 result returned"

Answer 5

can some explain these iam very much new to sql server. Iam getting so much of confuse how variables are evaluating

No, no one explain that. This is because this pattern has no defined correct result. That is, you get what you get. You often get a concatenated list, but it is by chance really.

As Tom Cooper said, use string_agg which is well-defined and works well. As he mentioned, it is available from SQL 2017 and on only. There is however an alternative for older versions as well, but it's anything but intuitive, so I will spare you from that method.