asked
SQL query help
**Can anybody please let me know why below query returns only B instead of AB ?** Declare @T1 table(C1 varchar(10) ) INSERT @T1 VALUES('A') INSERT @T1 VALUES('B') DECLARE @Str VARCHAR(MAX)='' SELECT @Str = @Str + C1 FROM @T1 ORDER BY LTRIM(RTRIM(C1)) Select @Str **Please let me know. Thanks in advance.**
That select doesn't 'loop around' updating the value of @Str as it goes, it's essentially running these 2 queries: @Str is initiallized to '' select @Str = '' + A select @Str = '' + B so the answer you get is the result of the last one To get what you want try select @Str = coalesce(@Str, C1) + C1 from @T1
Also wanted to add, this was raised as a bug on Connect, but MS confirmed that this is by design, and should not be relied upon :
http://connect.microsoft.com/SQLServer/feedback/details/606786/concatenation-into-a-scalar-variable-using-linked-server-in-from-clause-fails-when-casting-or-converting-data > ... we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution ...
@ajit.ghule if @Kev Riley's answer helped you out, please vote it up by clicking on the thumbs up next to it. If you feel that it gave you the definitive answer to your question, please click on the check box next to the answer.
Thanks for your quick reply. But that select does 'loop around' updating the value of @Str when I remove order by. Can you please let me know why this is happening ?
You'll also get 'AB' if you remove the LTRIM RTRIM : SELECT @Str = @Str + C1 FROM @T1 ORDER BY C1 This is due to the way the internal processing of the query is performed. If the original data set was large enough, the order of the resulting string would be unpredictable. It just so happens that 'A' and 'B' come out in the right order A related article can be found here :
http://support.microsoft.com/kb/287515/en-gb
13 People are following this question.
