In this post we are going to see how we can convert the Row values into comma separated String. Different SQL versions to achieve this (mentioned below), lets discuss one by one.

1. Using Coalesce and Substring Function
2. Using Replace Function
3. Using Stuff Function

Using Coalesce and Substring Function

In this example, we have converted the column values (RowNO, RowValues) from RowsIntoString table into String value with comma delimiter using coalesce and substring SQL functions.

drop table if exists RowsIntoString

create table RowsIntoString (RowNo int, RowValues nvarchar(100))
insert into RowsIntoString (RowNo, RowValues) 
values (1,'first'),(2,'second'),(4,'third'),(4,'fourth'),(5,'fifth'),(7,'sixth'),(7,'seventh')
,(7,'eighth'),(9,'nineth'),(10,'tenth'),(11,'eleventh')

select * from RowsIntoString

---using Coalesce---
DECLARE @CSkeycol VARCHAR(MAX), @CSkeycol2 VARCHAR(MAX)
select @CSkeycol=COALESCE(@CSkeycol + ', ', '')+cast(RowNo as nvarchar) from RowsIntoString
select @CSkeycol as RowsIntoStringValue

select @CSkeycol2=COALESCE(@CSkeycol2 + ''', ''', '')+RowValues from RowsIntoString
select ''''+@CSkeycol2+'''' as RowsIntoStringValue

---using Substring---
declare @tmp varchar(MAX)=''
declare @tmp2 varchar(MAX)=''
select @tmp = @tmp + cast(RowNo as nvarchar) + ', ' from RowsIntoString
select SUBSTRING(@tmp, 0, LEN(@tmp)) as RowsIntoStringValue

select @tmp2 = @tmp2 + RowValues + ''', ''' from RowsIntoString
select ''''+SUBSTRING(@tmp2, 0, LEN(@tmp2)-2) as RowsIntoStringValue

• 

Using Replace Function

In this example, we have converted the multi row string values into string value with comma delimiter using Replace SQL function.

declare @stg varchar(MAX)
set @stg='second
third
fourth
fifth
sixth
seventh'
select '''' + replace(@stg, char(10),''', ''') + '''' as RowsIntoStringValue
set @stg='1
2
3
4
5
6
7'
select replace(@stg, char(10),', ') as RowsIntoStringValue

• 

Using Stuff Function

In this example, we have Concatenated the Rowvalues with comma which is having same RowNo (in other words, concatenating the RowValues column if RowNo column has more than one values) using Stuff SQL functions.

select * from RowsIntoString where RowNo in (4,7)

select distinct t1.RowNo,
  STUFF(
         (SELECT ', ' + RowValues
          FROM RowsIntoString t2
          where t1.RowNo = t2.RowNo
          FOR XML PATH (''))
          , 1, 1, '')  AS RowValues
from RowsIntoString t1
where RowNo in (4,7)

• 

There is another post written by Latheesh which details another way with string_AGG.

Thanks for reading this blog post, If you enjoyed this, please share it with your friends!