0

I want to loop a table and convert into a single string. For which I used += The query goes as below.

Declare @a varchar(8000);
set @a = '';
select @a += col1+'.'+col2+' '+col3 --all are varchar columns
from table
where filter1 = <>
order by col1

I am getting only the last record from the list.

After trying so many different tweeks. Finally I used Top 1000 before the "@a +=" then the string got framed perfectly. Why am I seeing this behaviour? Note: The query has only 50 records. DB:SQL Server 14 Client: SSMS17 Thanks, Arun Kumar

Arun Kumar Krishnan
  • 1
  • 1
    Why use that syntax in the first place, is my question. There are *far* better methods of string concatenation that that method. – Thom A Sep 17 '20 at 10:03
  • 1
    Also, what does `filter1 = <>` mean? – Thom A Sep 17 '20 at 10:04
  • 7
    It's [documented](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-local-variable-transact-sql?view=sql-server-ver15#remarks) not to work - "Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. Because, all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row." – Damien_The_Unbeliever Sep 17 '20 at 10:05
  • @Damien_The_Unbeliever, +1 for the doc link. I didn't realize MS finally documented that aggregate string concatenation behavior is undefined. – Dan Guzman Sep 17 '20 at 10:24
  • 2
    @DanGuzman - that page (or something similar) has existed at least back to the 2000 version of the product. Problem is, it's the wrong page. Because you don't tend to look at the page for `SET @scalar_variable` when you're thinking about `SELECT`. – Damien_The_Unbeliever Sep 17 '20 at 10:30
  • Concatinate the variable to itself? `select @a = concat(@a,col1,col2,col3) ...` – LukStorms Sep 17 '20 at 10:46
  • Use STRING_AGG `Declare @a varchar(8000); select @a = STRING_AGG(col1+'.'+col2+' '+col3,'') from table where filter1 = <> order by col1` – Agneum Sep 17 '20 at 11:31

0 Answers0