November 7, 2012 at 5:51 am
why is the difference between both outputs ...
--SQL1:
Declare @tab table (seq int , k varchar(20))
declare @comma_Delimited varchar(max)
set @comma_delimited=''
insert into @tab
select 1,'s'
union all
select 2,'K'
union all
select 3,'t'
union all
select 4,'l'
select @comma_Delimited =@comma_Delimited + k + ',' from @tab order by 1
select @comma_Delimited
--SQL2.
Declare @tab table (seq int , k varchar(20))
declare @comma_Delimited varchar(max)
set @comma_delimited=''
insert into @tab
select 1,'s'
union all
select 2,'K'
union all
select 3,'t'
union all
select 4,'l'
select @comma_Delimited =@comma_Delimited + k + ',' from @tab order by seq
select @comma_Delimited
Why "Order by 1" is creating difference ...?
November 7, 2012 at 6:02 am
It's not a bug.
The syntax you are using is never reliable to get string concatenation in T-SQL.
BTW, Order By 1 in your first sample, is not inequivalent to Order By seq... It is equal to
order by @comma_Delimited + k + ','
November 7, 2012 at 6:05 am
ok......Got it thanks....:-)
November 7, 2012 at 6:27 am
If you want to generate a list of values from a column of values, like this, your best bet is to use For XML Path to do the work for you.
Bing/Google/whatever "concatenate string sql server for xml" or "sql server for xml path", and you'll find articles and tutorials on how to do this. It's very fast, effective, and quite a neat trick.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply