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!! 🙂

Advertisements

About Amogh Natu

Technology enthusiast, Associate Consultant @ Microsoft, music lover, love my guitar, Microsoft.NET, ASP.NET, C#.NET Professional.
This entry was posted in CodeProject, General, SQL Server and tagged , , , , . Bookmark the permalink.

Your comments or opinions.....

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s