Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Solution 1:

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_names
FROM information_schema.columns AS extern
CROSS 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_names
FROM   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) 

Solution 2:

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, '')

Solution 3:

SQL Server 2017 does introduce a new aggregate function

STRING_AGG ( expression, separator).

Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

The concatenated elements can be ordered by appending WITHIN GROUP (ORDER BY some_expression)

For versions 2005-2016 I typically use the XML method in the accepted answer.

This can fail in some circumstances however. e.g. if the data to be concatenated contains CHAR(29) you see

FOR XML could not serialize the data ... because it contains a character (0x001D) which is not allowed in XML.

A more robust method that can deal with all characters would be to use a CLR aggregate. However applying an ordering to the concatenated elements is more difficult with this approach.

The method of assigning to a variable is not guaranteed and should be avoided in production code.

Solution 4:

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 a
WHERE     empName IS NOT NULL
GROUP BY empName

Solution 5:

Have a look at the GROUP_CONCAT project on Github, I think I does exactly what you are searching for:

This project contains a set of SQLCLR User-defined Aggregate functions (SQLCLR UDAs) that collectively offer similar functionality to the MySQL GROUP_CONCAT function. There are multiple functions to ensure the best performance based on the functionality required...