How to get column values in one comma separated value

How to get column values in one comma separated value

K. M. Shawkat Zamil | October 12, 2022

Hello Developers,

In this demonstration, you will find how to make comma-separated columns from a data range. This problem occurs when you demonstrate wide range of dataset. Many of us try different approaches, but I found it easy to write and take less execution time in the compilation. Let's see.


Suppose I have a set of data like below:


Data Set
UserID Name
123456     Customer
123456     Target
123456     Beat
123456     Person
123456     Manager
123456     Test


Here we use FOR XML PATH in MSSQL Server. For that, we will put the data in a #table_name table. And follow the below code to get the output.


select distinct t.[UserID],
  STUFF((SELECT distinct ', ' + t1.Name
         from #table_name t1
         where t.[UserID] = t1.[UserID]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') as rows
from #table_name t;


If we execute the code in MSSQL Server then we get the desired output like the below:


123456 Customer, Target, Beat, Person, Manager, Test



Hope you can get help in the development journey.


Read More: How to get the first date and last date of the month from GETDATE() in mssql


K. M. Shawkat Zamil

K. M. Shawkat Zamil

Senior Software Engineer

I am a Senior Software Engineer in a reputed company in Bangladesh. I am a big fan of Laravel, PHP Programming, MSSQL Server, MySql, JavaScript, and lots more. I love to help people who are especially eager to learn. I believe in patience and motivation.