Search code examples
sql-servercoalesce

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

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

Solution

  • 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?