0

After running the following code, i am not getting my expected output, as you can see the Output 1 is incorrect.
The code: 

use master
go
set nocount on;
drop table if exists #t;
go
create table #t(
    ID smallint NOT NULL primary key,
    OutputValue varchar(max) NULL,
    OrderIndex int NULL
)
go
insert #t (ID, OutputValue, OrderIndex) values (3, N'V1', 1)
insert #t (ID, OutputValue, OrderIndex) values (6, N'V2', 2)
go
declare @s varchar(max) = '';
select @s = @s + OutputValue from #t where OrderIndex is not NULL order by OrderIndex;
print 'Output 1:' + @s;
go
declare @s varchar(max) = '';
select @s = @s + cast(OutputValue as varchar(8000)) from #t where OrderIndex is not NULL order by OrderIndex;
print 'Output 2:' + @s;
go
declare @s varchar(max) = '';
select @s = @s + OutputValue from #t order by OrderIndex;
print 'Output 3:' + @s;
go
declare @s varchar(max) = '';
select @s = @s + OutputValue from #t order by OrderIndex;
print 'Output 4:' + @s;
go
declare @s varchar(max) = '';
select @s = @s + OutputValue from #t where OrderIndex is not NULL;
print 'Output 5:' + @s;
/*
Output 1:V2
Output 2:V1V2
Output 3:V1V2
Output 4:V1V2
Output 5:V1V2
*/

What i should be getting is: 

Output 1:V1V2
Output 2:V1V2
Output 3:V1V2
Output 4:V1V2
Output 5:V1V2

What i am getting: 

Output 1:V2
Output 2:V1V2
Output 3:V1V2
Output 4:V1V2
Output 5:V1V2

Here is a testcase showing this issue

Ali Ahmadi
  • 701
  • 6
  • 27
  • all of your queries are wrong not only output 1.you are trying to combine 2 string but actually you are trying to combine scalar value with array output . – A Farmanbar Oct 06 '19 at 12:00
  • The referenced answer tells us why it doesn't work but doesn't offer advice as to how to achieve the desired result. For people reading this, look into the STUFF ... FOR XML PATH method as (at least) one way to concatenate strings in SQL record sets. e.g.: [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) – AlwaysLearning Oct 07 '19 at 00:08

0 Answers0