Return comma separated value with For XML Path in SQL Server 2008/2005

Thursday, August 6, 2009 |

We many time needs to return value of one column as a comma separated value, we can use COALESCE or ISNULL for this task but it is a lengthy process. I also have written one article for same task with COALESCE function, have a look:

Today I am going to show you one of the easy methods to do same task. Let us create one dummy table and move ahead.

use Adventureworks
CREATE TABLE [dbo].[emps](
[Name] [varchar](50),
[Dept] [varchar](10),
[Company] [varchar](15)
--insert records
SELECT 'Rajan','MIS','mar'
Now this is a time to show you how to get all names with separated by comma.

select left(,len( as 'allName' from
select name + ','  from emps for xml path('')
) as t(name)

Isn’t is easy to use method rather than ISNULL and COALESCE? Yes, it is!!!

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of


Anonymous said...

Stupid looking Picture

Ritesh Shah said...

thank you very much for comment and keep comming to my blog, actually I was eagerly waiting for your comment as you didn't give any since long!!!