How to Simulate MySQL's group_concat in SQL Server 2005?

Replicating MySQL's group_concat in SQL Server 2005

SQL Server 2005 lacks the convenient group_concat function found in MySQL, creating a challenge when needing to concatenate values within a group. While custom functions offer a solution, they can be cumbersome for those less familiar with their implementation.

A practical alternative utilizes SQL Server's system tables and the FOR XML PATH function. Here's an example:

<code class="language-sql">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;</code>

For improved handling of special characters, a more robust approach is:

<code class="language-sql">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);</code>

These methods effectively mimic group_concat without requiring complex user-defined functions, making the process simpler for developers less experienced with advanced SQL.

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