Build string from a SELECT statement [duplicate]

Question

Possible Duplicate:
SQL Server Group Concat with Different characters

I need an example on SELECT where the output is a ',' separated string

e.g. SELECT (... something ...) name AS output FROM name_table

gives me

output
-----------------------------
'Ann', 'Tom', 'Wilson', .....

How would you do that in SQL server 2008 R2?

Thank you!

Answer 1

Assuiming you have a schema like this,

CREATE TABLE Table1
    ([GROUP_ID] int, [PERSON_NAME] varchar(6));

INSERT INTO Table1
    ([GROUP_ID], [PERSON_NAME])
VALUES
    (1001, 'ALEX'),
    (1002, 'MATHEW'),
    (1001, 'GEORGE'),
    (1002, 'THOMAS'),
    (1001, 'JAMES');

create a query something like this to produce a comma separated value,

SELECT
     GROUP_ID,
     STUFF(
         (SELECT ', ' + PERSON_NAME
          FROM Table1
          WHERE [GROUP_ID] = a.GROUP_ID
          FOR XML PATH (''))
          , 1, 1, '')  AS NamesList
FROM Table1 AS a
GROUP BY GROUP_ID

• SQLFiddle Demo