how to convert rows of integers of a table to comma separated string in sql server

Question

I am trying to convert rows integers to comma separate string but it is giving an error like conversion failed.

Conversion failed when converting the varchar value '134, ' to data type int.

I tried this query

declare @CategoryIDs varchar(100)
select @CategoryIDs = COALESCE(@CategoryIDs+', ','')+ Val from #table

and the table values are like this

#table

Val
134
135
136

and i want output like this 134,135,136

Thanks, Raj

Answer 1

Try this:

declare @CategoryIDs varchar(100)
select @CategoryIDs = COALESCE(@CategoryIDs+', ','')+ CAST(Val AS varchar(100)) from #table

There are a number of ways to do string concatenation in SQL Server. This way works, but may or may not be optimally-performing for your particular use case. In general, I've found that the FOR XML PATH method works best. You can find a description of it here:

Building a comma separated list?

Answer 2

DECLARE @TABLE TABLE (Vals INT)
INSERT INTO @TABLE VALUES
(134),
(135),
(136)

    declare @CategoryIDs varchar(100) = '';   --<-- Assign to Empty string 
                                                 -- Otherwise concatenating to
                                                 -- null will always return null.

SELECT @CategoryIDs = COALESCE(@CategoryIDs+', ','')+ CAST(Vals AS VARCHAR(10))
from @TABLE

SELECT STUFF(@CategoryIDs, 1,2,'') 

The problem occurs when you try to concatinate an integer value to a string you need to convert that int value to varchar only then you can concatinate it to a string I have done this using CAST() function.

Using FOR XML PATH

SELECT STUFF((SELECT ', ' + CAST(Vals AS VARCHAR(100))
              FROM @TABLE
             FOR XML PATH(''),TYPE)
             .value('.','NVARCHAR(MAX)'),1,2,'')