Get comma separated values in SQL Server

We often require a column values to be assigned into a variable as comma separated. For this either we would prefer to create a scalar valued function for long term purpose or write a couple of line codes to achieve the same inside a stored proc or script. Earlier developers used to write CURSOR and/or while loop to make a column value as comma separated string, which is messy and more lines of code. Here I will demonstrate using XML PATH, Local variable and CASE WHEN. Apart from these, there are also other way around to get comma separated values in sql server. If you know any other methods for the same, then please feel free to share them up with us in comment section.

Using XML Path:

NULL value gets automatically handled in XML PATH as a blank string.

Using local variable:

Using CASE WHEN:

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: