How to Get SQL Column Values as Comma Separated String using XML Path Method

Introduction

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 ProductDetails Table:

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 string.

SELECT DISTINCT p.ProductName,
  STUFF((SELECT distinct ',' + p1.[Color]
         FROM ProductDetails p1
         WHERE p.ProductName = p1.ProductName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') Color
FROM ProductDetails p;

So in this way, we can fetch column value with comma separated string.