How variables concatenating rows into strings in sql server ?

srini_ksr 21 Reputation points
2022-07-13T10:40:06.93+00:00

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,

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-07-14T03:01:10.037+00:00

    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:
    220608-image.png

    Bert Zhou


4 additional answers

Sort by: Most helpful
  1. Tom Cooper 8,461 Reputation points
    2022-07-13T15:18:11.107+00:00

    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

    1 person found this answer helpful.

  2. Olaf Helper 39,181 Reputation points
    2022-07-13T10:53:53.697+00:00

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

    0 comments No comments

  3. Tom Phillips 17,711 Reputation points
    2022-07-13T12:18:48.817+00:00

    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"


  4. Erland Sommarskog 99,626 Reputation points MVP
    2022-07-13T20:53:15.55+00:00

    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.