How to Simulate MySQL's GROUP_CONCAT Function in SQL Server 2005?

In Microsoft SQL Server 2005, the group_concat function of Mysql simulated mysql

Question:

In the process of migrating mysql -based applications to Microsoft SQL Server 2005, the function of the group_concat function of MySQL needs to be copied. This function combines the multi -line in group query into a single comma -separated string.

Solution:

Although the SQL Server does not have the native equivalent of the GROUP_CONCAT function, it can use a combination of multiple technologies to simulate its function:

Use xml connection:

One method is to connect the string with XML, and then extract the result:

Use dynamic SQL:

<code class="language-sql">WITH ConcatenatedXML AS (
    SELECT empName,
           (
               SELECT ', ' + projID
               FROM project_members
               WHERE empName = e.empName
               FOR XML PATH('')
           ) AS projIDs
    FROM project_members AS e
    GROUP BY empName
)
SELECT empName,
       SUBSTRING(projIDs, 2, LEN(projIDs) - 2) AS group_concat_result
FROM ConcatenatedXML;</code>

Another method is to dynamically generate a query using the "" computing symbol connection string:

Use the user's custom function (UDF):

Finally, you can create a UDF that simulates the behavior of group_concat:

<code class="language-sql">DECLARE @SQL NVARCHAR(MAX) = 'SELECT empName, ''';

SELECT @SQL = @SQL + ', ' + projID
FROM project_members
WHERE empName = (
    SELECT TOP 1 empName
    FROM project_members
    GROUP BY empName
    HAVING COUNT(*) > 1
);

SET @SQL = @SQL + ''' AS group_concat_result
FROM project_members
GROUP BY empName;';

EXEC (@SQL);</code>

This udf can be used as follows:

The above is the detailed content of How to Simulate MySQL's GROUP_CONCAT Function in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!