Combining SELECT query results returns only one value

Question

I am using a select query along with a variable to concatenate some rows from the query, for example:

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

In my specific case, I changed the FROM to a view that simply distinguishes an integer column from nvarchar, so I don't need to drop it every time it appears in concatenation.

After making this change, the result of concatenation is one value from the table . When you move CAST to an external query or when you delete the ORDER BY results are expected.

All this can be reproduced using the following t-sql snippet.

 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 is SQL Server behaving like this? It makes no sense to me. I reproduced this on SQL Server 2005, 2008 and 2008R2.

Edit

This question is really a duplicate. The best answer so far given seems to be that Martin Smith is in this matter . Therefore, you can vote for closure.

Answer 1

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

This article presents some valid solutions to the string concatenation problem in a SELECT query: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Answer 2

Wow! At first you might think that this is a mistake. My opinion is lower after a lot of analysis.

I tested all my testing in the developer version of SQL Server 2012. Release the query plan to school.

- Typical order: = sort operation
select * from TTest
order from 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 creates a plan that looks right, 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 creates a plan without sorting, the wrong plan, but the correct results.

I think you really need to think about a logical way to interpret the SQL query.

1 - FROM
2 - WHERE 3 - GROUP BY
4 - FIX
5 - SELECTION
6 - ORDER

Thus, a derived query is computed first. We cannot order an additional request due to the following restriction:

The ORDER BY clause is not valid in views, built-in functions, views, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified.

You might think that (the query optimizer / transaction manager) computed a row and then noticed that it cannot be sorted because you have N rows, but 1 result.

In short, one would hope that both requests would either work correctly or fail. Since they do not receive the same plan, one request works and the other does not.