Simulating group_concat MySQL function in Microsoft SQL Server 2005?

No REAL easy way to do this. Lots of ideas out there, though.

Best one I've found:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_namesFROM information_schema.columns AS externCROSS APPLY(    SELECT column_name + ','    FROM information_schema.columns AS intern    WHERE extern.table_name = intern.table_name    FOR XML PATH('')) pre_trimmed (column_names)GROUP BY table_name, column_names;

Or a version that works correctly if the data might contain characters such as <

WITH extern     AS (SELECT DISTINCT table_name         FROM   INFORMATION_SCHEMA.COLUMNS)SELECT table_name,       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_namesFROM   extern       CROSS APPLY (SELECT column_name + ','                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern                    WHERE  extern.table_name = intern.table_name                    FOR XML PATH(''), TYPE) x (column_names)       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 

I may be a bit late to the party but this method works for me and is easier than the COALESCE method.

SELECT STUFF(             (SELECT ',' + Column_Name               FROM Table_Name              FOR XML PATH (''))             , 1, 1, '')

Possibly too late to be of benefit now, but is this not the easiest way to do things?

SELECT     empName, projIDs = replace                          ((SELECT Surname AS [data()]                              FROM project_members                              WHERE  empName = a.empName                              ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR)FROM         project_members aWHERE     empName IS NOT NULLGROUP BY empName