Concatenating results of SELECT query only returns one value

Question

I am using a select query together with a variable in order to concatenate some strings from a query, like so:

DECLARE @sConcat nvarchar(max)
SET @sConcat = ''
SELECT @sConcat = @sConcat + '[' + SomeColumn + ']'
 FROM SomeTable
 ORDER BY SomeColumn

In my particular case, I changed the FROM clause to a derived table, which just casts an integer column to nvarchar, so that I don't have to cast it every time it comes up in the concatenation.

After making this change, the result of the concatenation is just one value out of those in the table. When moving the CAST to the outer query, or when removing the ORDER BY clause, the results are as expected.

This all can be reproduced with the following piece of t-sql.

BEGIN TRAN

-- Create a dummy table and populate it with some values
CREATE TABLE TTest (
    TTest_ID int IDENTITY(1,1) NOT NULL,
    TTest_Text varchar(8) NOT NULL
 CONSTRAINT PK_TTest PRIMARY KEY CLUSTERED (
    TTest_Id ASC
 )
) ON [PRIMARY]

INSERT INTO TTest (TTest_Text) VALUES ('A')
INSERT INTO TTest (TTest_Text) VALUES ('B')
INSERT INTO TTest (TTest_Text) VALUES ('C')
INSERT INTO TTest (TTest_Text) VALUES ('D')
INSERT INTO TTest (TTest_Text) VALUES ('E')
INSERT INTO TTest (TTest_Text) VALUES ('F')
INSERT INTO TTest (TTest_Text) VALUES ('G')
INSERT INTO TTest (TTest_Text) VALUES ('H')

-- Create a string with the ID values of each row in brackets
DECLARE @sConcat nvarchar(max)

-- First attempt, produces the result '[8]' which is not what I expected
SET @sConcat = ''
SELECT @sConcat = @sConcat + '[' + TTest_ID + ']'
 FROM  (SELECT CAST(TTest_ID AS nvarchar(100)) AS TTest_ID, TTest_Text
         FROM TTest) TTestBis
 ORDER BY TTestBis.TTest_ID ASC

PRINT @sConcat

-- Second attempt, with cast in the outer query, 
-- produces the expected result '[1][2][3][4][5][6][7][8]'
SET @sConcat = ''
SELECT @sConcat = @sConcat + '[' + CAST(TTest_ID AS nvarchar(100)) + ']'
 FROM  (SELECT TTest_ID, TTest_Text
         FROM TTest) TTestBis
 ORDER BY TTestBis.TTest_ID ASC

PRINT @sConcat

-- Third attempt, same as first but without ORDER BY, 
-- also produces the expected result '[1][2][3][4][5][6][7][8]'
SET @sConcat = ''
SELECT @sConcat = @sConcat + '[' + TTest_ID + ']'
 FROM  (SELECT CAST(TTest_ID AS nvarchar(100)) AS TTest_ID, TTest_Text
         FROM TTest) TTestBis

PRINT @sConcat

ROLLBACK

Why does SQL Server behave this way? To me it does not make sense. I reproduced this on SQL Server 2005, 2008 and 2008R2.

Edit

This question is indeed a duplicate. The best answer given so far seems to be the one by Martin Smith in this question. So you may vote to close.

Answer 1

The question is indeed a duplicate. A comprehensive answer can be found in the following stackoverflow question: nvarchar concatenation / index / nvarchar(max) inexplicable behavior.

Several valid solutions to the problem of concatenating strings in a SELECT query are provided in this article: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/