Retrieving column values as a comma separated string in SQLServer

Hi,

This post is quite the opposite of the previous post. That is, in the previous post, We inserted a comma separated string into separate rows. Here we will return a single comma separated string by concatenating values from separate rows.

 

The query for this is very simple. Say, our table has two columns, UserId and UserName. And we want a list of usernames as comma-separated string, then the query would be

SELECT UserName + ‘,’

FROM TableName

FOR XML PATH(‘ ‘)

The “FOR XML PATH(‘some path’)” returns the result in the form of xml with each select result surrounded by xml tags named “some path”.

For example, if the parameter in “for xml path” is “response”,

the query would be

“SELECT UserName FROM TableName FOR XML PATH(‘response’).

The result of the query would be

<response>UserName1</response>

<response>UserName2</response>

<response>UserName3</response>

<response>UserName4</response>

<response>UserName5</response>

and so on,…………

So, in our case, because nothing is mentioned as parameter to the FOR XML PATH(‘ ‘) function (not even space), the result of the query would be

UserName1, UserName2, UserName3, UserName4, ……….. and so on…

You have your comma-separated result. 🙂

 

Hope this helps!! 🙂

Leave a Reply