Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is this a bug ??? Expand / Collapse
Author
Message
Posted Wednesday, November 07, 2012 5:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 15, Visits: 430
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
Posted Wednesday, November 07, 2012 6:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1381932
Posted Wednesday, November 07, 2012 6:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 15, Visits: 430
ok......Got it thanks....
Post #1381935
Posted Wednesday, November 07, 2012 6:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:34 PM
Points: 15,442, Visits: 9,588
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 »

Add to briefcase

Permissions Expand / Collapse