Is this a bug ???

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Is this a bug ???

Is this a bug ???

Rate Topic

Display Mode

Topic Options

Author

Message

Saurv

Posted Wednesday, November 07, 2012 5:51 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:04 AM
Points: 12, Visits: 346

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 ...?

Post #1381927

Eugene Elutin

Posted Wednesday, November 07, 2012 6:02 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 2,556, Visits: 4,395

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 + ','

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1381932

Saurv

Posted Wednesday, November 07, 2012 6:05 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:04 AM
Points: 12, Visits: 346

ok......Got it thanks.... Smile

Post #1381935

GSquared

Posted Wednesday, November 07, 2012 6:27 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1381944

« Prev Topic | Next Topic »

Permissions