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/

Answer 2

Wow! At first look, you might think this is a bug. My opinion is below after much analysis

I did all my testing in SQL Server 2012 developer version. Let's go to query plan school.

-- Typical order by = sort operation
select * from TTest
order by TTest_Text desc;

-- Code 1
DECLARE @sConcat nvarchar(max) = '';
SELECT @sConcat = @sConcat + '[' + D1.TTest_ID2 + ']'
FROM
(SELECT CAST(TTest_ID AS nvarchar(100)) AS TTest_ID2, TTest_Text FROM TTest) D1
ORDER BY D1.TTest_ID2;
PRINT @sConcat;

This produces the plan that looks correct but the wrong results.

-- Code 2
DECLARE @sConcat nvarchar(max) = '';
SELECT @sConcat = @sConcat + '[' + CAST(D2.TTest_ID AS nvarchar(100)) + ']'
FROM (SELECT TTest_ID, TTest_Text FROM TTest) D2
ORDER BY D2.TTest_ID ASC;
PRINT @sConcat;

This produces a plan without a sort, wrong plan, but the right results.

I think you really need to think about the logical way SQL interprets the request.

1 - FROM
2 - WHERE
3 - GROUP BY
4 - HAVING
5 - SELECT
6 - ORDER BY

Thus, the derived query is calculated first. We can not order the sub query because of the following restriction:

The ORDER BY clause is invalid in views, inline functions, derived tables, sub queries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

One would think that the (query optimizer / transaction manager) would calculate the string, then notice that it can not sort due to the fact you have N rows but 1 result.

In short, one would hope that both queries would either work correctly or fail. Because they do not get the same plan, one query works and another does not.