5

Is there a way to convert a single column record set such as

1
2
3

into '1,2,3' string in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matthew Rygiel
  • 1,247
  • 2
  • 15
  • 17
  • I know that I can use a cursor to do this but I want to know if there is a different way. – Matthew Rygiel Jan 12 '11 at 16:03
  • Does this have to be done at the sql level, or can you just take care of this on the consuming end programmatically? – RQDQ Jan 12 '11 at 16:06
  • Use `FOR XML PATH` or assign to a variable in a `SELECT`: possible duplicate of [How can I combine multiple rows into a comma-delimited list in SQL Server 2005?](http://stackoverflow.com/questions/180032/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-sql-server-2005) – Martin Smith Jan 12 '11 at 16:12
  • possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) – OMG Ponies Jan 12 '11 at 16:17

3 Answers3

7

I've had success creating a function using Coalesce like below to do this. You can then use the function in your select statement to get the delimited string. (You can add parameters to your function and use them in the coalesce select statement as needed.)

CREATE FUNCTION [dbo].[fn_MyFunction]
(
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @str NVARCHAR(MAX)

    DECLARE @Delimiter CHAR(2) 
    SET @Delimiter = ', '

    SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn
    FROM dbo.myTable

    RETURN RTRIM(LTRIM(@str))

END
TKTS
  • 1,261
  • 1
  • 11
  • 17
  • Wait so what if I wanted to coalesce on a different table than myTable? Are you going to create a function per table to save not having to remember coalesce or xml path? (I prefer coalesce myself but that's because I just use it a lot, the xml path method is a perfectly reasonable way to do it.) It's possible to create a SP do do this (multi table / multi column concatenation) but even then in my opinion it just seems like trying to hide what's actually happening) – Jason Benson Jan 12 '11 at 16:27
  • (typo, had xmp on the brain today so everything was xmp_path this and xmp_path that, corrected) – Jason Benson Jan 12 '11 at 16:31
  • Xml path works but automatically converts any special character to its entity reference (e.g. & becomes &) which may not be desirable. Plus you can have a fairly complicated select statement in your function if need be. And yes, you would have to create a new function for other tables so this may not be the optimal solution in all cases either. – TKTS Jan 12 '11 at 16:39
  • Also, the function solution works best if you want the delimited string to be one of multiple columns in your output. – TKTS Jan 12 '11 at 16:43
6
select stuff( (select ',' + YourColumn
               from YourTable
               for xml path('')), 1, 1, '')

Note: The stuff function just removes the first comma from the result string.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
5

COALESCE is decent way to do this

http://msdn.microsoft.com/en-us/library/ms190349.aspx

DECLARE @Out VARCHAR(2048)
Select @Out = COALESCE(@Out + ', ', '') + [YourColumn] from YourTable

print @Out
Jason Benson
  • 3,371
  • 1
  • 19
  • 21