Dealing with Comma Separated Values

Sometimes we need logic where you want to hold multiple row values in single Comma separated string.

Previously we were using cursor function to achieve this functionality but now a days in SQL 2012 we can achieve this functionality in simple and faster way.

Below are some tricks and function by using them you can achieve the functionality of converting multiple rows in one comma separated string and converting comma separated string into multiple rows.

Converting multiple rows in one comma separated string

1. Create Temp Table:

Create one temp table and insert sample record in it:

2. Query:

Create table #temp(email varchar(50))

insert into #temp values ( 'a.@gamil')

insert into #temp values( 'b.@gamil')
insert into #temp values( 'c.@gamil')

Select * from #temp

3. Write Sql COALESCE function for comma separated string

DECLARE @Names VARCHAR(8000)  

SELECT @Names = COALESCE(@Names + ',', '') + email   FROM #temp

Select @Names as Emails

By using COALESCE function and Concatenate function you can achieve concatenating "," and multiple rows

COALESCE is ANSI Standard function which is used to check Isnull value in Expression and it return other not null value if found Null in expression.

The Coalesce() function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.

The data type of a COALESCE expression is the data type of the input with the highest data type precedence. If all inputs are un-typed NULLs, then an error will be returned.

4. Write SQL STUFF function for comma separated string:

You can use STUFF function to achieve the above functionality.

Below is the syntax for the same:

Syntax: 

SELECT Col1, Email = 

    STUFF((SELECT ', ' + Email

           FROM your_table b 

           WHERE b.ReportId = a.ReportId 

          FOR XML PATH('')), 1, 2, '')

FROM your_table a

GROUP BY Col1

converting comma separated string into multiple rows.

1. Query

DECLARE @S varchar(max),

  @Split char(1),

  @X xml

SELECT @S = 'a.@gamil,b.@gamil,c.@gamil',

  @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

SELECT [Value] = T.c.value('.','varchar(20)')

FROM @X.nodes('/root/s') T(c)

Here we are converting comma separated string into format for XML tags and value by using Replace Function. After converting whole comma separated string into XML format, by using XML FROM @X.nodes('/root/s') T(c) query we are reading values from XML string and it returns values into multiple rows.

Note: While using this functionality we have to consider the limitation of SQL server Varchar datatype for size. If it exceed the limitation of 4000 size then you can not achieve the excepted functionality.