Converting Row values into String with delimiter in SQL Server

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!

One thought on “Converting Row values into String with delimiter in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s