Need output as 1,2,3,...10

Question

Need output as 1,2,3,...10

arup chakraborty

Hall of Fame

Points: 3640
More actions
January 15, 2009 at 4:11 am

#94451

I have a table with 10 rows. Datas of the rows are 1, then 2, then 3 ...10.
I want the output as 1,2,3,4,5 in one cell.
Please provide the SQL for it. I dont want to use cursor or temp tables.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Why do you want to concatenate the values? If that's for display, it's better to do it on the front end? If it's for storage, that's not a good way to store data within SQL server.

Why is there a restriction against temp tables?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Old McDonald SSCertifiable Points: 5768 More actions · Answer 2

DECLARE @RESULT varchar(128)

SELECT @RESULT = COALESCE(@RESULT, ', ' , '') + YourColumnName

FROM YourTableName

SELECT @RESULT

If the column is not char/varchar type the you will have to use cast

-Vikas Bindra

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

try this code hope this will help you...

declare @strCVS VARCHAR(500)

select @strCVS = coalesce(@strCVS + ',', '') + cast(object_id as varchar(10))

from sys.objects

select @strCVS

Abhijit - http://abhijitmore.wordpress.com

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 4

HI Avijit and Vikas,

Thanks for the help. This is exactly what I need.

Thanks again.

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 5

Hi Gail,

This is actualy a question, which I have faced in an interview. I have tried with ROW_Number function later but no result.

That's why I have posted it. Its easy to do the same using cursor or temp table, but also possibel through single SQL.

I have got my answer from Vikas and Avijeet.

Thanks again for your help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

arup_kc (1/15/2009)
Hi Gail,
This is actualy a question, which I have faced in an interview.

I thought as much.

Just note with the solutions above, there's no guarantee of order. It may come out in the order you want, it may not. There's a trick with FOR XML PATH that returns data the same way, can partition the comma-deliminated strings and does guarantee an order, when an ORDER BY is applied

Since it's an interview question, I'll leave you to google for it. It shouldn't be hard to find

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 7

arup chakraborty

Hall of Fame

Points: 3640

January 15, 2009 at 6:21 am

#927220

Thanks gail, I ll try your solution also.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 8

AS said this can be done by usning FOR XML path also....

SELECTSTUFF((

SELECTDISTINCT ', ' + cast(object_id as varchar(10))

FROMsys.objects

FOR XML PATH('')), 1, 1, '') AS 'Ids'

Abhijit - http://abhijitmore.wordpress.com

Rakesh_Singh Grasshopper Points: 21 More actions · Answer 9

hi Arup,

you can use the following code to get comma saperated data in a cell.

declare @Column1 varchar(8000)

set @Column1=''

select @Column1=@Column1+','+convert(varchar,Column1) from Table1

select substring(@Column1,2,len(@Column1))

Thanks & Regards

Rakesh Singh

bhushanvinay SSCrazy Points: 2253 More actions · Answer 10

Try this it may be simple.

Select Stuff (

SELECT ',' + CONVERT(ID AS VARCHAR(50))

FROM myIdTable

ORDER BY ID

FOR XML PATH('')

) , 1,1,'')

Regards
Vinay

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

arup_kc (1/15/2009)
Hi Gail,
This is actualy a question, which I have faced in an interview. I have tried with ROW_Number function later but no result.
That's why I have posted it. Its easy to do the same using cursor or temp table, but also possibel through single SQL.
I have got my answer from Vikas and Avijeet.
Thanks again for your help.

Actually, despite all of the seemingly correct answers you've been provided, you still don't have the correct answer... at least not in an interview with me. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

GilaMonster (1/15/2009)
Just note with the solutions above, there's no guarantee of order. It may come out in the order you want, it may not. There's a trick with FOR XML PATH that returns data the same way, can partition the comma-deliminated strings and does guarantee an order, when an ORDER BY is applied

Actually, unlike UPDATE pseudocursors, SELECT pseudocursors can use an ORDER BY:declare @strCVS VARCHAR(500)

select @strCVS = coalesce(@strCVS + ',', '') + cast(object_id as varchar(10))

from sys.objects

order by object_id desc

select @strCVS

which as I understand BOL should be effective:

ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query.

This being an ORDER BY on the outermost SELECT, it seems that it is guaranteed to be so sorted.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

RBarryYoung (1/17/2009)
This being an ORDER BY on the outermost SELECT, it seems that it is guaranteed to be so sorted.

Ummm. This is an odd case. I chatted with one of the QO team on this, and Itzik mentioned it in his pre-con at Pass as well as well. It's another case of 'usually works'. The concatenation (SELECT @var = @var + somecol) wasn't written to concat an entire table. It was written to do constant or variable concatenation. Usually it'll work, in odd circumstances the concatenation will be done before the order by and once in a while it doesn't work the way it's expected.

Bear in mind that the order by is the last cause applied, after the select is done.

I recall a post here (I can't remember who it was or who helped) where someone used this and when the order by was added, only one row was processed. (Were you involved in that?)

The xml path was written to do the concatenation, it's not an accident that it works that way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

As you say Gail, however, this is a case where, as documented, it is supposed to work.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]