Generate Comma Separated List with SELECT statement

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:



Happy learning......