Sql Server: Convert table Column data into comma separated string or row

SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields

Here  I am using COALESCE function Sql Server 2005/2008 to convert a field into comma separated value of string or row
My data in the table looks like :
 

The result I want to show is like :




Select query will be:

Assuming my table name is  #test, It has 2 column field1,field2

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ',' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

If you wish select all the rows of field2 as comma separated list then query will be:


SELECT STUFF( -- Remove first comma
    (
        SELECT  ', ' + field2 FROM -- create comma separated values
        (
          SELECT field2 FROM #test --Your query here
        ) AS T FOR XML PATH('')
    )
    ,1,1,'') AS field2

OR

DECLARE @test NVARCHAR(max)  
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2= @test 


Output: