SQL String aggregation returns different results based on location of variables

Question

My code:

if object_id('tempdb..#t1') is not null drop table #t1

create table #t1 (ID int, name varchar(10))

insert into #t1 values (1,'2'), (6,'2'), (6,'2'), (1,'4')

DECLARE @CHARS VARCHAR(100) = ''
SELECT @CHARS = @CHARS + name + ', ' -- <---CODE OF INTEREST
FROM #t1

This returns 2, 2, 2, 4,. All is well.

However, When I change the above code to append the comma and space first :

DECLARE @CHARS VARCHAR(100) = ''
SELECT @CHARS = ', ' + @CHARS + name -- <---CODE OF INTEREST
FROM #t1

SELECT @CHARS

it returns , , , , 2224. Shouldn't it return , 2, 2, 2, 4?

Answer 1

The logic of the second formula is: for every record it appends the string to the right side of @CHARS

Assume you have

insert into #t1 values (1,'2'), (6,'3'), (6,'4'), (1,'5')

to make it more clear

For the first row it returns ", 2"

for the second row, you @CHARS will be ", " + ", 2" + "3"

for the third row, @CHARS is ", , 23" so will set @CHARS to ", " + ", , 23" + "4"

try below queries to see what's going on:

DECLARE @CHARS VARCHAR(100) = ''
SELECT top 1 @CHARS = ', ' + @CHARS + name -- <---CODE OF INTEREST
FROM #t1

print @CHARS
go

Result: , 2
--------------------------
DECLARE @CHARS VARCHAR(100) = ''
SELECT top 2 @CHARS = ', ' + @CHARS + name -- <---CODE OF INTEREST
FROM #t1

print @CHARS
go

Result: , , 23
----------------------
DECLARE @CHARS VARCHAR(100) = ''
SELECT top 3 @CHARS = ', ' + @CHARS + name -- <---CODE OF INTEREST
FROM #t1

print @CHARS
go
Result: , , , 234