Select column values as comma seperated format

Question

Select column values as comma seperated format

David Kranes

SSCrazy

Points: 2523
More actions
January 14, 2011 at 1:06 pm

#230250

Hello, I need code that will select a single column from a table and put in into a variable in comma delimited format. Any help would be greatly appreciated.
Best Regards,
~David

Viewing 6 posts - 1 through 5 (of 5 total)

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

Something like this should do for a character data type column. A numeric datatype column would just need a cast to a varchar (or nvarchar).

DECLARE @MyList varchar(max)

SET @MyList = ''

SELECT @MyList = @MyList + ',' + MyColumn

FROM dbo.MyTable

SET @MyList = STUFF(@MyList, 1, 1, '')

toddasd SSCertifiable Points: 6298 More actions · Answer 2

This article was posted earlier this week:

http://qa.sqlservercentral.com/articles/comma+separated+list/71700/

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

andrewd.smith (1/14/2011)
Something like this should do for a character data type column. A numeric datatype column would just need a cast to a varchar (or nvarchar).
DECLARE @MyList varchar(max)
SET @MyList = ''
SELECT @MyList = @MyList + ',' + MyColumn
FROM dbo.MyTable
SET @MyList = STUFF(@MyList, 1, 1, '')

Ummmm.... be careful. It's widely documented that concatenation through a variable like that can be quite slow compared to XML concatenation.

--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)
Intro to Tally Tables and Functions

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

Thanks Jeff

I just compared the performance for a table with a few thousand rows, and you're quite right.

I use the method I presented because I sometimes write code that still needs to work on SQL Server 2000, and in the cases where I use this method, the number of rows being concatenated is never more than about 100. For tables with a large number of rows, I agree that the XML concatenation method is the way to go.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Agreed and thanks for the feedback, Andrew. SQL Server 2000 was quite the bear for things like concatenation. I did come up with a couple of ways to speed it up, though. Take a look at the following article...

http://qa.sqlservercentral.com/articles/Test+Data/61572/

--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)
Intro to Tally Tables and Functions