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.
- Using Coalesce and Substring Function
- Using Replace Function
- Using Stuff 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
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
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)
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”