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.
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.
COALESCE is a function that returns the first
nonnull expression among its arguments.
2.
Syntax: COALESCE ( expression [ ,…n ] )
Syntax: COALESCE ( expression [ ,…n ] )
3.
If all arguments are NULL,
COALESCE returns NULL.
If all arguments are NULL,
COALESCE returns NULL.
4.
COALESCE determines the type of the output
based on data type precedence.
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:
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 :
data from table :
DECLARE @ID VARCHAR (100)
–-Using
COALESCE function to get first not null value from all the argument
COALESCE function to get first not null value from all the argument
–Converting
Row data into comma separated string
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.
the above steps, we can convert the tabular row data into comma separated
string.
—————————————————-End
of Document—————————————————
of Document—————————————————