Tuesday, October 23, 2012

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 using COALESCE 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.
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---------------------------------------------------

No comments:

Post a Comment