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

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:

http://www.sqlhub.com/2008/04/coalesce-function-in-sql-server-for.html

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)

) ON [PRIMARY]

GO

--insert records

INSERT INTO emps

SELECT 'RITESH','MIS','ECHEM' UNION ALL

SELECT 'Rajan','MIS','mar'

Now this is a time to show you how to get all names with separated by comma.

select left(t.name,len(t.name)-1) 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
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com