Tag: Comma separated string in SQL Server

SQL, T-SQL

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!

SCRIPT GALLERY, SQL, SQL Server 2017

How to build comma separated string in SQL Server

Building a comma separated string is a very frequent&common requirement for SQL Server developers. Developers find different ways to achieve this like CLR/XML PATH/Co-related queries etc. With this post, we are going to see two options that are widely used XML PATH & string_AGG and a quick look at its performance comparison.

— Using STUFF & XML PATH

This is the most widely used method to build the comma separated string. In my personal experience, I observed many performance issues related to this method for a large set of data. 


Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')

SELECT  SessionID,STUFF((SELECT  ',' + CourseID FROM BuildCommaSeparatedString EE
            WHERE  EE.SessionID=E.SessionID
            ORDER BY CourseID -- To order the Courses in order 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM BuildCommaSeparatedString E
GROUP BY E.SessionID
Sample Execution results — Using string_AGG in SQL Server 2017

With SQL Server 2017 release, there is a better way to do this using string_agg built-in function. The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. 

STRING_AGG ( input_string, separator ) [ order_clause ]

input_string - is the column name of any type that can convert to a comma separated string
separator - is the separator caharacter like , or ; etc
[ order_clause ] - specifies the sort order of concatenated results using WITHIN GROUP clause

WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )
Please note that, The STRING_AGG() ignores NULL and it does not add the separator for NULL when performing concatenation. 

Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')

Select SessionID, STRING_AGG(CourseID,',') From BuildCommaSeparatedString Group by SessionID

--To build the comma separated string in an order way
Select SessionID, STRING_AGG(CourseID,',') within group (Order by CourseID asc) 
From BuildCommaSeparatedString Group by SessionID
Sample Execution results A quick look on Performance benefit

The below snapshot clearly indicates that the performance benefit of string_AGG built-in function over XML PATH approach.

Conclusion

If you are in SQL Server 2017 – use built-in function string_AGG instead of any other method. I have seen this outperform many times than a custom built code. If you do not agree or have any different experience, please share the specific case with an example for the benefits of readers. Thanks in advance!