Build string from a SELECT statement

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!

You need to post some idea of the data you're trying to query, and some sort of real attempt to solve the problem. `SELECT (... something ...)` is not a *real attempt*, and you've given no idea of what columns you have in `name_table`. Unless, of course, you're willing to accept `SELECT (... some_columns ...)` as an answer, in which case I'll post it now. :-) — Ken White, Dec 07 '12 at 03:19

score 6 · Accepted Answer · answered Dec 07 '12 at 03:18

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

Nice and easy! Answer picked! Thanks! – user1589188 Dec 07 '12 at 03:42 — user1589188, Dec 07 '12 at 03:42

Build string from a SELECT statement

1 Answers1