SQL Server- How to convert tabular data into comma separated string

1.     Background

This article describes how to convert the row data into single comma separated string. While developing software application, many times scenarios come to convert the tabular data to comma separated string. Here we are usingCOALESCE function to achieve the task.

2.     What is COALESCE?                       

1.      COALESCE is a function that returns the first nonnull expression among its arguments.

2.      Syntax: COALESCE ( expression [ ,...n ] )

3.      If all arguments are NULL, COALESCE returns NULL.

4.      COALESCE determines the type of the output based on data type precedence.

Data Type Precedence: http://msdn.microsoft.com/en-us/library/ms190309.aspx

5.      COALESCE(expression1,...n) is equivalent to the following CASE expression:

CASE

          WHEN (expression1 IS NOT NULL) THEN expression1

   WHEN (expression2 IS NOT NULL) THEN expression2

   ...

   ELSE expressionN

END

3.  Below is the script to convert tabular row data into comma separated string using COALESCE function:

Getting data from table :

 

 

--Declaring Variable

       DECLARE @ID VARCHAR (100)

--Using COALESCE function to get first not null value from all the argument

--Converting Row data into comma separated string

SELECT @ID = COALESCE (@ID,'') + CAST (ID AS VARCHAR (50)) +','

FROM Product

 

--Getting Comma separated string

SELECT @ID

4.     Conclusion

By using the above steps, we can convert the tabular row data into comma separated string.

----------------------------------------------------End of Document---------------------------------------------------