There are many scenarios where we have to show the values of column value as comma (or any other special character) separated
string in result set.
This can be easily achieved using XML path method in SQL.
Using the SQL Query
Let's take an example of
SELECT * FROM ProductDetails
Now suppose we want to fetch the list of products along with available colors where multiple colors are shown as comma separated
SELECT DISTINCT p.ProductName,
STUFF((SELECT distinct ',' + p1.[Color]
FROM ProductDetails p1
WHERE p.ProductName = p1.ProductName
FOR XML PATH(''), TYPE
FROM ProductDetails p;
So in this way, we can fetch column value with comma separated