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”